Conditional custom header concatenate

[izzleee]

See attached my excel spreadsheet for problem and desired solution:

• Customheaderpowerquery.xlsx (12.1 KB)

I want to concatenate W1, W2 etc onto the date column header. I want to keep the other column headers without any concatenation:

Below is the desired solution screenshot. I want to concatenate the cell into the header if it starts with Wk.

I know this is very awkward but I am not able to change the spreadsheet.

[BrianJ]

@izzleee ,

Once you’ve got the Problem table imported into Power BI, try dropping this into a new blank query:

let
    Source = Problem,
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    #"Transposed Table" = Table.Transpose(#"Promoted Headers"),
    #"Removed Columns" = Table.RemoveColumns(#"Transposed Table",{"Column1", "Column2"}),
    #"Replaced Value" = Table.ReplaceValue(#"Removed Columns",null,"""""",Replacer.ReplaceValue,{"Column4"}),
    #"Added Custom" = Table.AddColumn(#"Replaced Value", "Custom", each if Text.StartsWith( [Column4], "Wk" ) then Date.ToText( [Column3] )  & " - " &  [Column4] else [Column3]),
    #"Removed Columns1" = Table.RemoveColumns(#"Added Custom",{"Column3", "Column4"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns1",{"Custom", "Column5", "Column6"}),
    #"Transposed Table1" = Table.Transpose(#"Reordered Columns"),
    #"Promoted Headers1" = Table.PromoteHeaders(#"Transposed Table1", [PromoteAllScalars=true])
in
    #"Promoted Headers1"

The key steps are transposing the table and then adding this custom M code to do the conditional concatenation:

I hope this is helpful. Full solution file attached.

– Brian
eDNA Forum – Concatenated Header PQ Solution.pbix (27.1 KB)

[izzleee]

Thanks Brian this is very helpful as always.