Excel: Power Query Tips

Combining All Files from a Folder

Important: After combining the files, do not start adding rules in "Other Queries" > "Templates".

Instead, you should work on the Transform Sample File to first strip out any extra headers you may have:

Elevating a row to a heading

Important: If you're combining multiple files, you need to work on the "Transform Sample File" instead of "Other Queries" > "Templates".

Follow this step to elevate the top rows:

Extracting a Cell and making it a column

In Power Query you can reference a value with the following construct (https://gorilla.bi/power-query/get-previous-row-value/):

= #"StepName"[ColumnName]{ ZeroBasedIndex }

The zero based index indicates which row to retrieve, whereas the ColumnName shows which column to get the data from. 

Example: To retrieve date value from the second row you can add to a custom column:

1. Add Column > Custom Column.

2. = #"PreviousStepName"[Date]{ 1 }

Important: if you are combining multiple files, this step should occur after the headings are elevated, otherwise the column value will be elevated and break in all the other files. And #"StepName" should refer to the step before the rows are elevated to headings. For instance:

let

    Source = Excel.Workbook(Parameter1, null, true),

    #SheetName = Source{[Item="SheetName",Kind="Sheet"]}[Data],

    #Step1 = DO SOMETHING IN THIS STEP

    #Step2 = Table.Skip(#Step1, 4),

    #Step3 = Table.PromoteHeaders(#Step2, [PromoteAllScalars=true]),

    #Step4 = Table.AddColumn(#Step3, "Subsystem", each #Step1[Date]{1})

in

    #Step4