Combine columns with same data in

Hi mwinds ,

SInce you have more columns I did the following:

• Deleted all the columns except the measures and the property ID
• Did all the steps until the expanded like in the previous option
• Add a column wiht the merge of property ID and the index value
• Removed the Count property ID and Index value
• Did the Pivot by the new column of the merge
• Extracted the property ID na coluna merge
• Made a merge with the stpe Changed Type (4th step on the query) by property ID
• Expande all other columns we need:

let
    Source = Excel.Workbook(File.Contents("C:\Teste\Testdata.xlsx"), null, true),
    #"Property Reporting_Sheet" = Source{[Item="Property Reporting",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(#"Property Reporting_Sheet", [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Property ID", type any}, {"Property Type", type any}, {"Pre EPC", type any}, {"Cost of all measures", Int64.Type}, {"Government Funding", type number}, {"LA Funding (Non-SHDF)", type number}, {"Self-funding", type number}, {"No. of measures to install", Int64.Type}, {"Property Installations Status", type text}, {"Post EPC", type text}, {"RSL", type text}, {"LEP", type any}, {"LA", type any}, {"Measure 1", type any}, {"Measure 1 cost", type number}, {"Measure 1 Status", type text}, {"Measure 2", type any}, {"Measure 2 cost", type number}, {"Measure 2 Status", type text}, {"Measure 3", type any}, {"Measure 3 cost", type number}, {"Measure 3 Status", type text}, {"Measure 4", type any}, {"Measure 4 cost", type number}, {"Measure 4 Status", type text}, {"Measure 5", type any}, {"Measure 5 cost", Int64.Type}, {"Measure 5 Status", type text}, {"Measure 6", type any}, {"Measure 6 cost", Int64.Type}, {"Measure 6 Status", type text}, {"Completed Measures", Int64.Type}, {"Measures not started", Int64.Type}, {"Measures Started", Int64.Type}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Property Type", "Pre EPC", "Cost of all measures", "Government Funding", "LA Funding (Non-SHDF)", "Self-funding", "No. of measures to install", "Property Installations Status", "Post EPC", "RSL", "LEP", "LA", "Completed Measures", "Measures not started", "Measures Started", "Not reported"}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Removed Columns", {"Property ID"}, "Attribute", "Value"),
    #"Inserted Text After Delimiter" = Table.AddColumn(#"Unpivoted Columns", "Type", each Text.AfterDelimiter([Attribute], " ", 1), type text),
    #"Replaced Value" = Table.ReplaceValue(#"Inserted Text After Delimiter","","Measure",Replacer.ReplaceValue,{"Type"}),
    #"Grouped Rows" = Table.Group(#"Replaced Value", {"Type"}, {{"Count", each _, type table [Property ID=number, Attribute=text, Value=any, Text After Delimiter=text]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn ([Count], "IndexValue",1)),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Property ID", "Value", "IndexValue"}, {"Property ID", "Value", "IndexValue"}),
    #"Inserted Merged Column" = Table.AddColumn(#"Expanded Custom", "Merged", each Text.Combine({Text.From([Property ID], "en-150"), Text.From([IndexValue], "en-150")}, "|"), type text),
    #"Changed Type1" = Table.TransformColumnTypes(#"Inserted Merged Column",{{"Property ID", Int64.Type}}),
    #"Removed Columns1" = Table.RemoveColumns(#"Changed Type1",{"Count", "Property ID", "IndexValue"}),
    #"Pivoted Column" = Table.Pivot(#"Removed Columns1", List.Distinct(#"Removed Columns1"[#"Type"]), "Type", "Value"),
    #"Extracted Text Before Delimiter" = Table.TransformColumns(#"Pivoted Column", {{"Merged", each Text.BeforeDelimiter(_, "|"), type text}}),
    #"Renamed Columns" = Table.RenameColumns(#"Extracted Text Before Delimiter",{{"Merged", "Property ID"}}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Renamed Columns",{{"Property ID", Int64.Type}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type2",{{"Property ID", Order.Ascending}}),
    #"Merged Queries" = Table.NestedJoin(#"Sorted Rows", {"Property ID"}, #"Changed Type", {"Property ID"}, "Sorted Rows", JoinKind.LeftOuter),
    #"Expanded Sorted Rows" = Table.ExpandTableColumn(#"Merged Queries", "Sorted Rows", {"Property Type", "Pre EPC", "Cost of all measures", "Government Funding", "LA Funding (Non-SHDF)", "Self-funding", "No. of measures to install", "Property Installations Status", "Post EPC", "RSL", "LEP", "LA", "Completed Measures", "Measures not started", "Measures Started", "Not reported"}, {"Property Type", "Pre EPC", "Cost of all measures", "Government Funding", "LA Funding (Non-SHDF)", "Self-funding", "No. of measures to install", "Property Installations Status", "Post EPC", "RSL", "LEP", "LA", "Completed Measures", "Measures not started", "Measures Started", "Not reported"})
in
    #"Expanded Sorted Rows"