I have a data set imported into PQ/Excel from XML that has this form-
Type Name Value
measure name depth
measure svmbol d
measure value Fathom
measure name width
measure svmbol w
measure value metre
measure property Length
The data shape is clear and quite structured, although the number of attributes does vary from 3 to 5 per item (measure).
I want to transform the name/value pairs in the second two columns into a table form-
Type Name Symbol Value Property
measure depth d fathom
measure width w metre Length
Seems as if it should be simple, but Transpose wants to transpose the whole table only, while Pivot of the Name column, which looks like the right tool, either wants to aggregate (count) the data, which is no use to me, or fails if you choose the "do not aggregate" option with the error "too many elements in the enumeration".
I think I could achieve the result, laboriously, by creating four custom columns with logic in them, but that seems excessive. Is there a way to use Pivot or other functions to do this easily?
You need a unique counter that changes each time [Name]=name before pivoting
Add column .. Index column
Add column .. Custom Column ... with formula
if [Name]="name" then [Index] else null
Right click the new custom column and fill down
Right click the index column and Remove it
Click the name column then Transform ... Pivot column ... value column is Value and in Advanced choose Dont Aggregate
Right click the extra column and remove it
let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Added Index1" = Table.AddIndexColumn(Source, "Index", 0, 1),
#"Added Custom1" = Table.AddColumn(#"Added Index1", "Custom", each if [Name]="name" then [Index] else null),
#"Filled Down1" = Table.FillDown(#"Added Custom1",{"Custom"}),
#"Removed Columns" = Table.RemoveColumns(#"Filled Down1",{"Index"}),
#"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[Name]), "Name", "Value")
in #"Pivoted Column"