Combine<\/strong> button<\/li>\n<\/ol>\n![\"Graphical](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2021\/01\/graphical-user-interface-text-application-descr-1.png\")
<\/p>\n
This screen has the traditional Transform<\/strong> and Load<\/strong> buttons but also has the Combine<\/strong> button, which has both options, Transform<\/strong> and Load<\/strong>, below it.<\/p>\nThe traditional Transform<\/strong> and Load<\/strong> will be dealing with the list of files inside the Azure Storage folder. From this point, it will be our decision what to do with each file.<\/p>\nThe Combine button, on the other hand, will bring to us a pre-built M script to combine all the files in the folder. It\u2019s easy to mistake this feature believing Power BI will read only the current files, but in fact the script is flexible in such a way to read all the files in the folder, even future files included there.<\/p>\n
\n- Select the option Combine & Transform<\/strong><\/li>\n
- Click Transform Data<\/strong> button<\/li>\n<\/ol>\n
The M Code \u2013 How it Works<\/h2>\n
On Power Query<\/strong> window, you may notice the pre-built steps in the Applied Steps<\/strong> window. It\u2019s also very interesting the way the queries were built: The final query is in a folder called Other Queries<\/em> while you also have a folder called Helper Function<\/em> containing a parameterized function.<\/p>\n![\"Graphical](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2021\/01\/graphical-user-interface-application-word-descr.png\")
<\/p>\n
Let\u2019s analyze the M code to better understand how it works. Using the menu View<\/strong>-> Advanced Editor<\/strong> you can access the M code.<\/p>\nThis is how our M code looks like:<\/p>\n
\n
let<\/span>
\n\u00a0\u00a0Source<\/span>\u00a0=<\/span>\u00a0AzureStorage.DataLake<\/span>(<\/span>“https:\/\/lakedemo.dfs.core.windows.net\/opendatalake\/trips”<\/span>)<\/span>,<\/span>
\n\u00a0\u00a0#”Filtered Hidden Files1″<\/span>\u00a0=<\/span>\u00a0Table.SelectRows<\/span>(<\/span>Source<\/span>,<\/span>\u00a0each<\/span>\u00a0[<\/span>Attributes<\/span>]<\/span>?<\/span>[<\/span>Hidden<\/span>]<\/span>?<\/span>\u00a0<><\/span>\u00a0true<\/span>)<\/span>,<\/span>
\n\u00a0\u00a0#”Invoke Custom Function1″<\/span>\u00a0=<\/span>\u00a0Table.AddColumn<\/span>(<\/span>
\n\u00a0\u00a0\u00a0\u00a0#”Filtered Hidden Files1″<\/span>,<\/span>
\n\u00a0\u00a0\u00a0\u00a0“Transform File”<\/span>,<\/span>
\n\u00a0\u00a0\u00a0\u00a0each<\/span>\u00a0#”Transform File”<\/span>(<\/span>[<\/span>Content<\/span>]<\/span>)<\/span>
\n\u00a0\u00a0)<\/span>,<\/span>
\n\u00a0\u00a0#”Renamed Columns1″<\/span>\u00a0=<\/span>\u00a0Table.RenameColumns<\/span>(<\/span>#”Invoke Custom Function1″<\/span>,<\/span>\u00a0{<\/span>“Name”<\/span>,<\/span>\u00a0“Source.Name”<\/span>}<\/span>)<\/span>,<\/span>
\n\u00a0\u00a0#”Removed Other Columns1″<\/span>\u00a0=<\/span>\u00a0Table.SelectColumns<\/span>(<\/span>
\n\u00a0\u00a0\u00a0\u00a0#”Renamed Columns1″<\/span>,<\/span>
\n\u00a0\u00a0\u00a0\u00a0{<\/span>“Source.Name”<\/span>,<\/span>\u00a0“Transform File”<\/span>}<\/span>
\n\u00a0\u00a0)<\/span>,<\/span>
\n\u00a0\u00a0#”Expanded Table Column1″<\/span>\u00a0=<\/span>\u00a0Table.ExpandTableColumn<\/span>(<\/span>
\n\u00a0\u00a0\u00a0\u00a0#”Removed Other Columns1″<\/span>,<\/span>
\n\u00a0\u00a0\u00a0\u00a0“Transform File”<\/span>,<\/span>
\n\u00a0\u00a0\u00a0\u00a0Table.ColumnNames<\/span>(<\/span>#”Transform File”<\/span>(<\/span>#”Sample File”<\/span>)<\/span>)<\/span>
\n\u00a0\u00a0)<\/span>,<\/span>
\n\u00a0\u00a0#”Removed Columns”<\/span>\u00a0=<\/span>\u00a0Table.RemoveColumns<\/span>(<\/span>#”Expanded Table Column1″<\/span>,<\/span>\u00a0{<\/span>“Source.Name”<\/span>}<\/span>)<\/span>,<\/span>
\n\u00a0\u00a0#”Grouped Rows”<\/span>\u00a0=<\/span>\u00a0Table.Group<\/span>(<\/span>
\n\u00a0\u00a0\u00a0\u00a0#”Removed Columns”<\/span>,<\/span>
\n\u00a0\u00a0\u00a0\u00a0{<\/span>“Month”<\/span>}<\/span>,<\/span>
\n\u00a0\u00a0\u00a0\u00a0{<\/span>{<\/span>“Trips”<\/span>,<\/span>\u00a0each<\/span>\u00a0List.Sum<\/span>(<\/span>[<\/span>Trips<\/span>]<\/span>)<\/span>,<\/span>\u00a0type<\/span>\u00a0number<\/span>}<\/span>}<\/span>
\n\u00a0\u00a0)<\/span>
\nin<\/span>
\n\u00a0\u00a0#”Grouped Rows”<\/span><\/div>\n<\/div>\nThese are the steps this code is executing:<\/p>\n
\n- Filter all files, making sure to not include hidden files<\/li>\n
- Use the AddColumn<\/strong> method to call the function \u201cTransform File\u201d<\/strong> for each row<\/li>\n
- Remove additional columns, leaving only the file name and result of the function<\/li>\n
- Expands the column containing the result of the function<\/li>\n<\/ul>\n
This main script calls the Transform File function for each file in the folder. There is no fixed file name, all the files will be transformed and returned. This means that at any time new files are included in this data lake folder, a simple refresh will bring the data to the dashboard, leaving the solution flexible as a client solution for a data lake needs to be.<\/p>\n
The M<\/strong> code for the Transform File<\/strong> function is this:<\/p>\n\n
let<\/span>
\n\u00a0\u00a0\u00a0\u00a0Source<\/span>\u00a0=<\/span>\u00a0Parquet.Document<\/span>(<\/span>Parameter1<\/span>)<\/span>,<\/span>
\n\u00a0\u00a0\u00a0\u00a0#”Changed Type”<\/span>\u00a0=<\/span>\u00a0Table.TransformColumnTypes<\/span>(<\/span>Source<\/span>,<\/span>{<\/span>{<\/span>“DateID”<\/span>,<\/span>\u00a0type<\/span>\u00a0text<\/span>}<\/span>}<\/span>)<\/span>,<\/span>
\n\u00a0\u00a0\u00a0\u00a0#”Added Custom”<\/span>\u00a0=<\/span>\u00a0Table.AddColumn<\/span>(<\/span>#”Changed Type”<\/span>,<\/span>\u00a0“Month”<\/span>,<\/span>\u00a0each<\/span>\u00a0Text.Middle<\/span>(<\/span>[<\/span>DateID<\/span>]<\/span>,<\/span>4<\/span>,<\/span>2<\/span>)<\/span>)<\/span>,<\/span>
\n\u00a0\u00a0\u00a0\u00a0#”Grouped Rows”<\/span>\u00a0=<\/span>\u00a0Table.Group<\/span>(<\/span>#”Added Custom”<\/span>,<\/span>\u00a0{<\/span>“Month”<\/span>}<\/span>,<\/span>\u00a0{<\/span>{<\/span>“Trips”<\/span>,<\/span>\u00a0each<\/span>\u00a0Table.RowCount<\/span>(<\/span>
![](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2021\/01\/word-image-62.png\")
<\/p>\n![\"Graphical](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2021\/01\/graphical-user-interface-text-application-descr.png\")
<\/p>\n