How to assign variable in ‘Headers'

Question

I have Report where the dates are as column headers.

The report is set as "show last x weeks" so each week the date should shift by 7 days, but it causes an issue down the line as PowerQuery fixes the header name as a constant into the code and does not update accordingly.

So this week report should start with 10/12/18 date range and end with 11/02/19.

 = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"Project 
Location Name", "Role Name", "03/12/2018", "10/12/2018",
"17/12/2018", "24/12/2018", "31/12/2018", "07/01/2019", "14/01/2019",
"21/01/2019", "28/01/2019", "04/02/2019", "Total"}, {"Project Location
Name", "Role Name", "03/12/2018", "10/12/2018", "17/12/2018",
"24/12/2018", "31/12/2018", "07/01/2019", "14/01/2019", "21/01/2019",
"28/01/2019", "04/02/2019", "Total"})

Please edit your question and use Control-K to format your code. Thank you! — grooveplex

chillin chillin · Accepted Answer · 2019-01-21T15:44:38

One way to do this might be by changing this line:

Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"Project Location Name", "Role Name", "03/12/2018", "10/12/2018", "17/12/2018", "24/12/2018", "31/12/2018", "07/01/2019", "14/01/2019", "21/01/2019", "28/01/2019", "04/02/2019", "Total"}, {"Project Location Name", "Role Name", "03/12/2018", "10/12/2018", "17/12/2018", "24/12/2018", "31/12/2018", "07/01/2019", "14/01/2019", "21/01/2019", "28/01/2019", "04/02/2019", "Total"})

to:

let
    headersToExpand = List.Distinct(List.Combine(List.Transform(#"Converted to Table"[Column1], Record.FieldNames))),
    expandDynamically = Table.ExpandRecordColumn(#"Converted to Table", "Column1", headersToExpand)
in
    expandDynamically

Assuming:

every row in "Column1" contains records,
your previous "step" is #"Converted to Table",
the column of records (that you want to expand) is called "Column1",

then the headersToExpand bit should give you a list of unique record field names (rather than the hard coded list you had) -- which can then be used in the expandDynamically step.

How to assign variable in ‘Headers'

1 Answers