Conditional Column Header in Power Query

Question

I have a situation where my Header titles are as missing for some columns due to the fact that my data has KEY and TEXT format where there is a column header is available for KEY but nut for TEXT. An EXAMPLE below.

I would like to get header defined for every columns in such a way that the logic works even if I change the position of the data set. In excel I do it as in the image 2.

And the result as in image 3

.

Currently I am playing around with PowerQuery and I am quite new to this tool. Does anyone know the steps that could work around so even if the position of data source change, column header remain as it works in the excel. I would be very glad.

virtualdvid virtualdvid · Accepted Answer · 2018-10-08T20:34:16

Here a possible solution:

In Excel load the table to Power Query uncheck My table has headers:

In the Power Query editor look for Advanced Editor and copy and paste this lines:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type any}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}, {"Column8", type any}, {"Column9", type any}, {"Column10", type any}}),
    #"Transposed Table" = Table.Transpose(#"Changed Type"),
    #"Added Conditional Column" = Table.AddColumn(#"Transposed Table", "Custom", each if [Column1] = null then [Column2] else [Column1]),
    #"Duplicated Column" = Table.DuplicateColumn(#"Added Conditional Column", "Custom", "Custom - Copy"),
    #"Filled Down" = Table.FillDown(#"Duplicated Column",{"Custom"}),
    #"Added Conditional Column1" = Table.AddColumn(#"Filled Down", "Custom.1", each if [#"Custom - Copy"] = null then [Custom] & " Name" else [Custom]),
    #"Reordered Columns" = Table.ReorderColumns(#"Added Conditional Column1",{"Custom.1", "Column1", "Column2", "Column3", "Column4", "Column5", "Custom", "Custom - Copy"}),
    #"Removed Columns" = Table.RemoveColumns(#"Reordered Columns",{"Column1", "Custom", "Custom - Copy"}),
    #"Transposed Table1" = Table.Transpose(#"Removed Columns"),
    #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table1", [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"StudentID", type any}, {"Country", type text}, {"Country Name", type text}, {"Department", type text}, {"Department Name", type text}, {"Mayor", type text}, {"Mayor Name", type text}, {"AGE", type any}, {"WEIGHT", type any}, {"HEIGHT", type any}})
in
    #"Changed Type1"

Load as table in Excel:

Conditional Column Header in Power Query

1 Answers