Search
  • Marek Vavrovic

Append tables

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:



4 views0 comments