Concatenate columns in Power Query without splitting into multiple queries and adding
I have a few reports that I process with a power request for which I have a solution, but I hope the wizards have a better method.
In the example below, my current method is to split into 3 requests, just a connection, with a name and record number, and also with one element and value (remove .1, .2, .3 so that the header of the record is identical). Then I add them to one query, pivoting the data and then loading them into a table. For some files, I will do this many times, and in order to reprocess requests, I will have to copy each one separately (I am not good with function calls). Is there a better way to do this in a single request, preferably through an interface so that I can share with my teammates who are even newer than me? I know there is potential in the grouping function, but I am not very good at using these tools (yet). See below google sheet with examples of tables:
https://docs.google.com/spreadsheets/d/14f-7GjUMwwzcUj9sAFBxaPjLnOW_1hKBYPtelRHfr70/edit?usp=sharing
source to share
Interest Ask. I was able to do this in one request after several 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 of explanation:
- Unpivot in all position columns
- Unpivot on all Value columns (So here it will create nxn rows of records)
- Filter records where ItemKey matches ValueKey (e.g. Item.1 = Value.1, etc.)
- Remove ItemKey and ValueKey columns
- Rotate in column Item as header with value as value
Not the best solution as it creates additional records, but much less manual work, which should reflect your concern.
PS # Filtered Rows includes some built-in functionality that is not available in the UI, but you may need to tweak it if your actual data contains more than 9 value pairs for each record. (since I am only comparing the last character of the keys)
source to share
What do I see:
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 writing the formula in the "Custom Added" step.
This will work with any reasonable number of item.x-value.x column pairs.
source to share