(2018-July-15) Your case is to create an Excel template with data extracted from a backend SQL Server database; it takes a few minutes and Power Query is a very handy tool to connect/shape and extract your data into a worksheet format. What if you need to create multiple files where your SQL bases data source is only one parameter different (like a report date or a product name).
In my case, I have a list of 7000 geo stations across the globe, with my default extract logic I was able to pull a list of Canadian stations in my Excel file. Just connect to a SQL Server using Power Query, and the job is done. But that is still part of the problem to solve.
What if you need to create individual files for each of the main parameter's values, in my case, it's a country name. Let's explore if we can add some flexibility to the existing Power Query with Excel-based parameters.
1) A new Power Query is created based on my existing Excel table by pressing the "From Table" option:
2) One conversion step is removed from this table in Power Query:
3) A specific cell is selected from sourcing Excel table, which converts the table into a scalar value in Power Query:
4) A Custom function is then created based on the sourcing scalar value:
5) Then an explicit filtering condition in the main data query is replaced with the newly created function:
6) And now I can choose any country from the list that I want and see their geo stations, even Greenland :-)
Happy data adventures!
In my case, I have a list of 7000 geo stations across the globe, with my default extract logic I was able to pull a list of Canadian stations in my Excel file. Just connect to a SQL Server using Power Query, and the job is done. But that is still part of the problem to solve.
What if you need to create individual files for each of the main parameter's values, in my case, it's a country name. Let's explore if we can add some flexibility to the existing Power Query with Excel-based parameters.
1) A new Power Query is created based on my existing Excel table by pressing the "From Table" option:
2) One conversion step is removed from this table in Power Query:
3) A specific cell is selected from sourcing Excel table, which converts the table into a scalar value in Power Query:
4) A Custom function is then created based on the sourcing scalar value:
5) Then an explicit filtering condition in the main data query is replaced with the newly created function:
6) And now I can choose any country from the list that I want and see their geo stations, even Greenland :-)
Happy data adventures!
Comments
Post a Comment