In this exercise I am going to show how to append tables using M code.
I have 3 tables with the same metadata. These tables are not formated as table. Additionally I am extracting the file path using excel formulas: SUBSTITUTE(LEFT(CELL("filename",A1),SEARCH("]",CELL("filename",A1))-1),"[","").
-Using Define Name to call the result of this formula as FilePath.
My simple goal is to get all 3 tables into power query. Append them and load them back to separate sheet or data model.
Step1: Data > Get Data > From Other Soruces > Blank Query
This will kick me off to power query.
Step2: =Excel.CurrentWorkbook()
Returns the contents of the current Excel workbook. I have returned the file path created by Define Name as a table. This needs to be transformed into a text, so I can use File.Contents to extract the content of this file.
Step3: =Excel.CurrentWorkbook(){0}[Content]
{0} positional operator
[Content] field access operator
Step4: click on fx to add new step. =Source
Step5: =Source{0}[Column1]
In step5 I have added 2 operators to retrieve 1. row and 1. column. The file path is formated as text. I can use File.Contents function to extract this file as binary and then Excel.CurrentWorbook will return the Excel objects.
Step6: = File.Contents(Source{0}[Column1])
This will return the file as binary
Step7: = Excel.Workbook( File.Contents(Source{0}[Column1]))
Returns the contents of the Excel workbook.
Excel.Workbook(workbook as binary, optional useHeaders as any, optional delayTypes as nullable logical) as table
Step8: Filter out unnecessary objects. Keep just tables you need.
Step9: click on the expand button
Step10: Finish the transformations...
M code:
let
Source = Excel.CurrentWorkbook(){0}[Content],
Custom1 = Excel.Workbook( File.Contents(Source{0}[Column1]),true),
#"Filtered Rows" = Table.SelectRows(Custom1, each [Name] <> "FilePath" and [Name] <> "FilePath1"),
#"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows",{"Data"}),
#"Expanded Data" = Table.ExpandTableColumn(#"Removed Other Columns", "Data", {"Period", "Type of client", "Client name", "Revenue ($ 000')", "Cogs ($ 000')"}, {"Period", "Type of client", "Client name", "Revenue ($ 000')", "Cogs ($ 000')"}),
#"Changed Type" = Table.TransformColumnTypes(#"Expanded Data",{{"Period", type date}, {"Type of client", type text}, {"Client name", type text}, {"Revenue ($ 000')", type number}, {"Cogs ($ 000')", type number}})
in
#"Changed Type"
Final table:
