PowerQuery Table Transformation

0
votes

I want to be able to make the first column in my table pair with all others, making a new row for every combination of the two.

I need to be able to turn this:

  • A | 1 | 2 | 3
  • B | 4 | 5 | 6
  • C | 6 | 7 | 9

Into this:

  • A | 1
  • A | 2
  • A | 3
  • B | 4
  • B | 5
  • B | 6
  • C | 7
  • C | 8
  • C | 9

Is there any way this can be done using just powerquery?

2
powerquery

2 Answers

0
votes

Just unpivot other columns:

unpivot = Table.UnpivotOtherColumns(Source, {"col1"}, "a", "b")[[col1],[b]]
0
votes

Are they in separate columns? Then load into powerquery, right click first column, choose unpivot other columns.

Are they in a single column separated by |s? Then use below

Right click column, split column by delimiter, Select or enter delimeter --Custom-- |, Split at each occurrence of the delimiter, ignore advanced options

Remove the latter part of the query so that = Table.SplitColumn(#"Changed Type", "Column1", Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv), {"Column1.1", "Column1.2", "Column1.3", "Column1.4"}),

becomes = Table.SplitColumn(#"Changed Type", "Column1", Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv))

right Click first Column, unpivot other columns

right Click attribute column, remove column

Assuming data is in Table1 with no column headers or header column of Column1 then code is

let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
#"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "Column1", Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv)),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Split Column by Delimiter", {"Column1.1"}, "Attribute", "Value"),
#"Removed Columns" = Table.RemoveColumns(#"Unpivoted Other Columns",{"Attribute"})
in #"Removed Columns"