I have a function which generates the source table on the left. I now wish to transform this table by merging the route and custom column and then pivot this on value such that these rows are now the column names for the values.

When I did this, however, I didn't anticipate that the hazard assessment conclusion would itself become a column name, as shown below:

I wish in some way to modify the logic such that if hazard assessment conclusion is then dependent on the route value, blank cells are replaced with this value.

Since the headers are generated from merged column names, I am confident there is a logical connection between the two. I just have been struggling since yesterday to figure it out.

Importantly I am looking for a dynamic solution, I appreciate you could just merge the columns in this case, but in reality, I will have hundreds of rows in the same format as the example source table.

M Code:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Merged Columns" = Table.CombineColumns(Source,{"Route", "Custom"},Combiner.CombineTextByDelimiter("#(lf)", QuoteStyle.None),"Merged"),
    #"Pivoted Column" = Table.Pivot(#"Merged Columns", List.Distinct(#"Merged Columns"[Merged]), "Merged", "Value")
in
    #"Pivoted Column"

Data:

CodePudding user response：

Try this. It populates all combinations of Route/Custom against all names, then fills in the blanks with Hazard assessment if there is no corresponding value

let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"Route", type text}, {"Custom", type text}, {"Value", type text}}),
#"Filtered Rows3" = Table.SelectRows(#"Changed Type", each ([Custom] = "Hazard assessment conclusion")),
#"Removed Other Columns" = Table.SelectColumns(#"Changed Type",{"Route", "Custom"}),
#"Removed Duplicates" = Table.Distinct(#"Removed Other Columns"),
#"Filtered Rows" = Table.SelectRows(#"Removed Duplicates", each ([Custom] <> "Hazard assessment conclusion")),
#"Removed Other Columns1" = Table.SelectColumns(#"Changed Type",{"Name"}),
#"Removed Duplicates1" = Table.Distinct(#"Removed Other Columns1"),
#"Added Custom" = Table.AddColumn(#"Removed Duplicates1", "x", each #"Filtered Rows"),
#"Expanded x" = Table.ExpandTableColumn(#"Added Custom", "x", {"Route", "Custom"}, {"Route", "Custom"}),
#"Merged Queries" = Table.NestedJoin(#"Expanded x", {"Name", "Route", "Custom"}, #"Changed Type", {"Name", "Route", "Custom"}, "Table1", JoinKind.LeftOuter),
#"Expanded Table1" = Table.ExpandTableColumn(#"Merged Queries", "Table1", {"Value"}, {"Value"}),
#"Merged Queries1" = Table.NestedJoin(#"Expanded Table1", {"Name", "Route"}, #"Filtered Rows3", {"Name", "Route"}, "Table3", JoinKind.LeftOuter),
#"Expanded Table3" = Table.ExpandTableColumn(#"Merged Queries1", "Table3", {"Value"}, {"Alt.Value"}),
#"Added Custom1" = Table.AddColumn(#"Expanded Table3", "Value2", each if [Value]=null then [Alt.Value] else [Value]),
#"Merged Columns" = Table.CombineColumns(#"Added Custom1",{"Route", "Custom"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Merged"),
#"Removed Columns" = Table.RemoveColumns(#"Merged Columns",{"Value", "Alt.Value"}),
#"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[Merged]), "Merged", "Value2")
in  #"Pivoted Column"