Combining Columns in Power Query without splitting into multiple queries and appending

Question

I have some reports that I am processing with power query, that I have a solution for, but I am hoping the wizards here have a better method.

In the example below, my current method is to split into 3 queries, connection only, with the name and record number, as well as a single Item and Value (remove the .1, .2, .3 so the header records are identical) I would then append them together into one query, pivot the data, then load to a table. For some files, I'll do this many times, and to recycle the queries, I'd have to copy each one separately (I'm not good with invoking functions). Is there a better way to do this within one query, and preferably through the interface so I can share with my teammates that are even newer than me? I know there is potential in the grouping function, but I am not great at using those tools (yet). See below for a google sheet with example tables:

https://docs.google.com/spreadsheets/d/14f-7GjUMwwzcUj9sAFBxaPjLnOW_1hKBYPtelRHfr70/edit?usp=sharing

Answer 1

Interesting question. I managed to do it in a single query after a few trials.

Query:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ZY49D4MgEIb/C7MLYL9m07GNadwMw9UwmKLXACb67z1sbaAMR9477rk8bcuafmAF45zTW+ux6w0FSVWB6SYDHm34p2r07J+IL4pHpooUfUxG74sVDm9wjpIInYUFRwrnDLqj19+LYfNqwW1Hyo/LNg7MDewSGiFiyfJfUqSSp5zdLUViKWPLS05FmjLWPPw0JVNqBQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Name = _t, Record = _t, Item.1 = _t, Value.1 = _t, Item.2 = _t, Value.2 = _t, Item.3 = _t, Value.3 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"Record", Int64.Type}, {"Item.1", type text}, {"Value.1", Int64.Type}, {"Item.2", type text}, {"Value.2", Int64.Type}, {"Item.3", type text}, {"Value.3", Int64.Type}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Name", "Record", "Value.1", "Value.2", "Value.3"}, "Attribute", "Value"),
    #"Renamed Columns" = Table.RenameColumns(#"Unpivoted Columns",{{"Attribute", "ItemKey"}, {"Value", "Item"}}),
    #"Unpivoted Columns1" = Table.UnpivotOtherColumns(#"Renamed Columns", {"Name", "Record", "ItemKey", "Item"}, "Attribute", "Value"),
    #"Renamed Columns1" = Table.RenameColumns(#"Unpivoted Columns1",{{"Attribute", "ValueKey"}}),
    #"Filtered Rows" = Table.SelectRows(#"Renamed Columns1", each Text.EndsWith([ItemKey], Text.End([ValueKey], 1))),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"ItemKey", "ValueKey"}),
    #"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[Item]), "Item", "Value", List.Sum)
in
    #"Pivoted Column"

Result:

A bit explanation:

• Unpivot on all Item columns
• Unpivot on all Value columns (So here it'll create n x n rows of records)
• Filter on records where ItemKey matches with ValueKey (e.g. Item.1 = Value.1, etc)
• Remove ItemKey and ValueKey columns
• Pivot on Item column as header with Value as value

Not the best solution as it creates extra records, but much less manual work involved, which should address your concern.

P.S. The #"Filtered Rows" involves some built-in functions which are not available in the UI though, and you may need to customize it if your actual data has more than 9 item-value pairs for each record. (as I'm only comparing the last character of the keys)

Answer 2

That's how I see it:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],

//Add index for each row of source column. Use Add Index Column
    #"Added Index" = Table.AddIndexColumn(Source, "Index", 1, 1), 
//Unpivot table 
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Added Index", {"Index", "Name", "Record"}, "Attribute", "Value"),

//At this step I corrected default column names in formula editor. 
// Also, it is important to have only 1 dot in column names. 
    #"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Other Columns","Attribute",Splitter.SplitTextByEachDelimiter({"."}, QuoteStyle.Csv, false),{"ColumnName", "ColumnIndex"}), 
//As a result we get old column names clean of numbers in one column, and index of each column in another

//Next we combine row index and column index in order to generate identifier for new row. Add custom column, write Text.From([Index]) & Text.From([ColumnIndex]) in formula window
    #"Added Custom" = Table.AddColumn(#"Split Column by Delimiter", "Idx", each Text.From([Index]) & Text.From([ColumnIndex])), 

//some cleanup
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Idx", "Name", "Record", "ColumnName", "Value"}), 

// Pivot columns back. Without row identifier this won't work! 
    #"Pivoted Column" = Table.Pivot(#"Removed Other Columns", {"Item", "Value"}, "ColumnName", "Value"), //Step on ColumnNames, select Pivot, Value as values column, Don't Aggregate as function.
    #"Removed Columns" = Table.RemoveColumns(#"Pivoted Column",{"Idx"}),
//Finally, Pivot to the desired look. 
    #"Pivoted Column1" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[Item]), "Item", "Value", List.Sum) //Step on Item, select Pivot, Value as values column, Sum or Don't Aggregate as function.
in
    #"Pivoted Column1"

This will require some formula writing at step "Added Custom".

This will work with any reasonable number of item.x-value.x column pairs.