Skip to main content

Data Adventures

My personal journey in this intricate world of data and continuous efforts to make it more structured and well understood - Rayis Imayev.

Search This Blog

Showing posts with the label Power Query

Posts

SQL Server data flow into Excel template with Power Query function-based parameter

Posted by Rayis Imayev on July 15, 2018

Get link
Facebook
X
Pinterest
Email
Other Apps

Power BI with Azure Data Lake - where the water doesn't run dry

Posted by Rayis Imayev on February 04, 2018

Get link
Facebook
X
Pinterest
Email
Other Apps

Get Data experience from Excel to Power BI and SQL Server Analysis Services 2017

Posted by Rayis Imayev on January 20, 2018

Get link
Facebook
X
Pinterest
Email
Other Apps

When it’s cold outside, perhaps Power BI heat maps can warm you up

Posted by Rayis Imayev on January 13, 2018

Get link
Facebook
X
Pinterest
Email
Other Apps

Data modeling in Power BI using XML file with Parent and Children nodes

Posted by Rayis Imayev on August 08, 2017

Get link
Facebook
X
Pinterest
Email
Other Apps

Calculating Travel Distance/Time in Power BI using Bing Map API

Posted by Rayis Imayev on July 24, 2017

Get link
Facebook
X
Pinterest
Email
Other Apps

Labels

Azure Data Factory Azure Power BI Data ADF Pipeline JSON Maps Variables Array

Azure Logic App Data Modeling DevOps CI/CD Excel Open Data Parameter Power Query SQL Server Visualization continuous integration Azure Function Data Factory Mapping Data Flows Metadata continuous delivery ADF. Azure Data Factory Analytics Durable Function Email Notification deployment ArcGIS ArgGIS Map Data Vault Flatten GIS GitHub Latitude Longitude PostgreSQL QGIS SQL Shape Map Trigger API Attachment Azure Data Lake Store Azure DevOps Big Data Bing Map Blob CSV Custom Shapefile Data Flow Database Databricks ESRI Error File Management Load Mapbox PASS Power Automate Flow REST API SQL Azure SQL Azure Database SSMS T-SQL Template WGS 84 Webhook Wrangling data flows XML 200 202 3D Map ADLS ARM Template Alerts Alter ow Animation Apache Spark Append Variable Architect Architecture Azure Data Studio Azure Databricks Azure Monitor Azure Service Bus Azure Storage Blob Storage Container Build CDS CRM Calendar Cloud Column mapping Common Data Services Condition Custom Maps Custom Solution Data Architecture Data Lake Data Source Data Systems Data warehouse DataForACause Database Design Default Value Delete Delete Activity Distance Drain mode Dual axes EPSG 4326 ER Studio Execution Explode Extension Fail Filled Map Filter Flow map Function Gallery Template Geometry Git Global Fishing Globe Map HDFS HTML HTTP Heatmap IF IF activity ISO Icon Map Ingestion Integration JSONB Lambda Architecture Latency Learning Log Analytics Logic Operation Look Data M language Macro Maintenance Merge Metrics Microsoft Dynamics 365 Migration Monitor Narrative Science Network ODBC Parameterized Parent Child Nodes Parse Performance Personal Story Polling Polygon Post Deployment Power App Power Map PowerShell Predictive Analytics Primary Secondary axis Pulse Chart Python Queue Reference Data Reference Layer Release Resource Manager SQL Saturday SQL Server Project SSAS SSRS Salesforce Scale Controller Send Message SharePoint Shared Dataset Solution Template Source Control Spark Spatial Data Spatial Functions Special character Statistics Status Code Stored Procedure Support Switch Activity System Variables Tabular Template Gallery Temporal table Throughput Time Time Slider TopoJSON Twitter UX Unlearning Upgrade Usability VS Studio Validation Variable Version Web Week arm-template-parameters-definition.json custom parameter dbdiagram dbeaver er studio data architect erwin data modeler long running process microseconds oracle sql developer data modeler scale in scale out self-hosted agent timestamp

Show more Show less

Rayis Imayev

Follow @RayisImayev

Archive

2024 3
- September 1
  - What I learned volunteering at a children's summer...
- March 2

2023 5
- October 1
- July 1
- April 2
- February 1
2022 11
- August 2
- July 1
- June 2
- May 2
- March 1
- January 3
2021 20
- December 1
- November 1
- October 1
- September 1
- July 2
- June 2
- May 3
- April 1
- March 2
- February 3
- January 3
2020 21
- December 2
- October 2
- September 2
- August 1
- July 1
- June 2
- May 2
- April 3
- March 4
- February 1
- January 1
2019 19
- December 1
- November 1
- October 3
- June 1
- May 1
- April 2
- March 3
- February 4
- January 3
2018 16
- December 1
- November 2
- October 2
- August 1
- July 3
- May 2
- March 1
- February 2
- January 2
2017 12
- December 1
- November 1
- October 1
- September 1
- August 1
- July 1
- June 1
- May 1
- April 1
- March 1
- February 1
- January 1
2016 9
- November 1
- October 1
- September 1
- August 1
- July 1
- June 1
- May 1
- April 1
- February 1
2015 10
- December 1
- November 1
- October 1
- August 1
- July 2
- June 1
- May 1
- April 1
- March 1
2014 11
- December 1
- November 1
- October 2
- August 1
- July 3
- April 1
- March 1
- February 1

Show more Show less

Labels

2001 2021 3D Map1 ADF14 ADF. Azure Data Factory4 ADLS1 Alerts1 Alter ow1 Analytics4 Animation1

Apache Spark1 API2 Append Variable1 ArcGIS3 Architect1 Architecture1 ArgGIS Map3 ARM Template1 arm-template-parameters-definition.json1 Array7 Attachment2 Azure23 Azure Data Factory50 Azure Data Lake Store2 Azure Data Studio1 Azure Databricks1 Azure DevOps2 Azure Function5 Azure Logic App7 Azure Monitor1 Azure Service Bus1 Azure Storage1 Big Data2 Bing Map2 Blob2 Blob Storage Container1 Build1 Calendar1 CDS1 CI/CD6 Cloud1 Column mapping1 Common Data Services1 Condition1 continuous delivery5 continuous integration6 CRM1 CSV2 Custom Maps1 custom parameter1 Custom Shapefile2 Custom Solution1 Data15 Data Architecture1 Data Factory5 Data Flow2 Data Lake1 Data Modeling7 Data Source1 Data Systems1 Data Vault3 Data warehouse1 Database2 Database Design1 Databricks2 DataForACause1 dbdiagram1 dbeaver1 Default Value1 Delete1 Delete Activity1 deployment4 DevOps7 Distance1 Drain mode1 Dual axes1 Durable Function4 Email Notification4 EPSG 43261 ER Studio1 er studio data architect1 Error2 erwin data modeler1 ESRI2 Excel6 Execution1 Explode1 Extension1 Fail1 File Management2 Filled Map1 Filter1 Flatten3 Flow map1 Function1 Gallery Template1 Geometry1 GIS3 Git1 GitHub3 Global Fishing1 Globe Map1 HDFS1 Heatmap1 HTML1 HTTP1 Icon Map1 IF1 IF activity1 Ingestion1 Integration1 ISO1 JSON9 JSONB1 Lambda Architecture1 Latency1 Latitude3 Learning1 Load2 Log Analytics1 Logic Operation1 long running process1 Longitude3 Look Data1 M language1 Macro1 Maintenance1 Mapbox2 Mapping Data Flows5 Maps8 Merge1 Metadata5 Metrics1 microseconds1 Microsoft Dynamics 3651 Migration1 Monitor1 Narrative Science1 Network1 ODBC1 Open Data6 oracle sql developer data modeler1 Parameter6 Parameterized1 Parent Child Nodes1 Parse1 PASS2 Performance1 Personal Story1 Pipeline11 Polling1 Polygon1 Post Deployment1 PostgreSQL3 Power App1 Power Automate Flow2 Power BI23 Power Map1 Power Query6 PowerShell1 Predictive Analytics1 Primary Secondary axis1 Pulse Chart1 Python1 QGIS3 Queue1 Reference Data1 Reference Layer1 Release1 Resource Manager1 REST API2 Salesforce1 Scale Controller1 scale in1 scale out1 self-hosted agent1 Send Message1 Shape Map3 Shared Dataset1 SharePoint1 Solution Template1 Source Control1 Spark1 Spatial Data1 Spatial Functions1 Special character1 SQL3 SQL Azure2 SQL Azure Database2 SQL Saturday1 SQL Server6 SQL Server Project1 SSAS1 SSMS2 SSRS1 Statistics1 Status Code1 Stored Procedure1 Support1 Switch Activity1 System Variables1 T-SQL2 Tabular1 Template2 Template Gallery1 Temporal table1 Throughput1 Time1 Time Slider1 timestamp1 TopoJSON1 Trigger3 Twitter1 Unlearning1 Upgrade1 Usability1 UX1 Validation1 Variable1 Variables8 Version1 Visualization6 VS Studio1 Web1 Webhook2 Week1 WGS 842 Wrangling data flows2 XML2

Show more Show less

Popular Posts

Working with Arrays in Azure Data Factory

(2019-June- 06) I remember that I had a professor at my university who would often encourage his students to learn and get more experienced with simple things first. Learn the basics in and out and then move forward to more complicated concepts and practices; that was his motto, and he really tried to share this idea with us. Previously published blog posts about using Variables in Azure Data Factory: - Setting Variables in Azure Data Factory Pipelines - Append Variable activity in Azure Data Factory: Story of combining things together - System Variables in Azure Data Factory: Your Everyday Toolbox - Azure Data Factory: Extracting array first element Simple things sometimes can be overlooked as well. With the addition of Variables in Azure Data Factory Control Flow (there were not available there at the beginning), Arrays have become one of those simple things to me. Image by Magnascan from Pixabay

Executing Azure Data Factory pipelines by Power App / Automate Flow

(2021-Jun-30) One of my university professors liked to tell us a quote, “ The Sleep of Reason Produces Monsters ”, in a way to help us, his students, to stay active in our thinking process. I’m not sure if Francisco Goya , had a similar aspiration when he was creating his artwork with the same name. So, let me explain my reasons to create a solution to trigger Azure Data Factory (ADF) pipelines from a Power App and why it shouldn't be considered as a monster :-)

Transforming JSON data with the help of Azure Data Factory - Part 4 - Passing Arrays around

(2020-May- 24) It has never been my plan to write a series of articles about how I can work with JSON files in Azure Data Factory (ADF). While working with one particular ADF component I then had discovered other possible options to use richness and less constrained JSON file format, which in a nutshell is just a text file with one or more ( " key " : " value " ) pair elements. Sometimes you could have an array of those pair elements or an array of other arrays; now you see where I'm going with the name of my current blog post :-)

Azure Data Factory: Extracting array first element

(2019-Apr- 28) Full credit goes to Microsoft for the latest efforts updating Azure products with new features and documenting corresponding changes for the end users. Azure Data Factory (ADF) is a great example of this. A user recently asked me a question on my previous blog post ( Setting Variables in Azure Data Factory Pipelines ) about possibility extracting the first element of a variable if this variable is set of elements (array). So as a spoiler alert, before writing a blog post and adding a bit more clarity to the existing Microsoft ADF documentation, here is a quick answer to this question. The first element of an ArrayVariable is extracted by this expression: @variables(' ArrayVariable ')[0] . I created a simple ADF pipeline to test this out:

Blog post about Copying Azure files to SharePoint, comparing Logic App and Power Automate Flow for this, and Trust No One

(2021-May-31) Recently, I created a prototype solution for one of the projects, that would copy data files from Azure to SharePoint location. The task is trivial, doesn't require a lot of effort to build such a workflow with the help of the Azure Logic App. However, SharePoint itself is like another ecosystem and it may throw a curveball to me and slow down my solution development timeline dramatically. This post is a reminder to myself on how I had spent 5% of my time to develop this solution and then the other 95% to understand why it wasn't working initially to later resolve it.

Setting default values for Array parameters/variables in Azure Data Factory

(2020-Aug-14) Setting default values for my parameters or variables in Azure Data Factory (ADF) may be a trivial task, but it gets more interesting when those elements are Arrays. The recent addition of Global Parameters to the Data Factory by Microsoft may create an extra thrill to learn how to set values for such parameters too: https://docs.microsoft.com/en-ca/azure/data-factory/author-global-parameters . Photo by Mihis Alex from Pexels

Transforming JSON to CSV with the help of Flatten task in Azure Data Factory

(2020-Mar- 19) Recently, Microsoft introduced a new Flatten task to the existing set of powerful transformations available in the Azure Data Factory (ADF) Mapping Data Flows - https://docs.microsoft.com/en-us/azure/data-factory/data-flow-flatten . What this new task does it helps to transform/transpose/flatten your JSON structure into a denormalized flatten datasets that you can upload into a new or existing flat database table. 2020-Mar-26 Update: Part 2 : Transforming JSON to CSV with the help of Flatten task in Azure Data Factory - Part 2 (Wrangling data flows) I like the analogy of the Transpose function in Excel that helps to rotate your vertical set of data pairs ( name : value ) into a table with the column name s and value s for corresponding objects. And when this vertical JSON structural set contains several similar sets (array) then ADF Mapping Data Flows Flatten does a really good job by transforming it into a table with several rows (records).

Continuous integration and delivery (CI/CD) in Azure Data Factory using DevOps and GitHub

(2019-Feb- 18) With Azure Data Factory (ADF) continuous integration, you help your team to collaborate and develop data transformation solutions within the same data factory workspace and maintain your combined development efforts in a central code repository. Continuous delivery helps to build and deploy your ADF solution for testing and release purposes. Basically, the CI/CD process helps to establish a good software development practice and aims to build a healthy relationship between development, quality assurance, and other supporting teams. Update 2020-Mar-15: Part 2 of this blog post published in 2020-Jan-28: http://datanrg.blogspot.com/2020/01/continuous-integration-and-delivery.html Update 2019-Jun- 24: Video recording of my webinar session on Continuous Integration and Delivery (CI/CD) in Azure Data Factory at a recent PASS Cloud Virtual Group meeting.

Azure DevOps: Merging code to Main branch from a specific branch only

(2022-July-25) I really liked reading printed PC Magazines ( https://www.pcmag.com/ ) or Computerworld newspapers ( https://www.computerworld.com/ ) when I was in school. Those magazines pictured IT System or Database Admins as a special group of IT professionals. Big IT vendor paper ads usually portrayed them in a clean business-casual dress code with button-down collar shirts being surrounded by tall server racks with flickering lights. On another side, if you were part of the development or data analyst team without sufficient knowledge of IT infrastructure maintenance and management practice, then connecting with such an elite workforce team and seeking their help was slightly difficult, however, that wasn’t always the case. Things change: print editions of magazines are discontinued and the dress code is no longer a required sign of an IT group membership. So in real life, one of the ways to fight this “imposter” syndrome is to step out of my comfort zone and learn new things rel

Passing array of arrays as a parameter in Azure Data Factory and the role of an @item element in this

(2020-Sep- 13) Array of arrays, in a JSON world, it’s a very common concept to have a set of sets of other elements in a dataset. Despite how strange it may sound, our real life is filled with similar analogies: books arranged by categories in a library, plates stacked up in a cupboard, or even someone’s closet has many sections that are filled with different clothing items. Arrays are part of our daily life too :-) Photo by Lovefood Art from Pexels

Twitter Widget

Tweets by RayisImayev

Search This Blog

Posts

Posts

All Comments

All Comments

Powered by Blogger