Transpose values in column based on key value in other column

Question

I'd like to make a kind of tranposition for the following input table based on column B5. In output show unique values of B5 and tranpose all values in B3 related with the same value in B5.

+----+----+-------+----+----+
| B1 | B2 |   B3  | B4 | B5 |
+----+----+-------+----+----+
|  R |  1 |  1624 |  M | 11 |
+----+----+-------+----+----+
|  R |  1 |  4606 |  M | 12 |
+----+----+-------+----+----+
|  R |  1 |  4609 |  M | 12 |
+----+----+-------+----+----+
|  R |  1 |  4630 |  M | 12 |
+----+----+-------+----+----+
|  R |  1 |  4690 |  M | 12 |
+----+----+-------+----+----+
|  R |  1 | 25660 |  M | 27 |
+----+----+-------+----+----+
|  R |  1 | 73003 |  M | 28 |
+----+----+-------+----+----+
|  R |  1 | 73006 |  M | 28 |
+----+----+-------+----+----+
|  R |  1 | 73008 |  M | 28 |
+----+----+-------+----+----+
|  R |  1 | 73013 |  M | 28 |
+----+----+-------+----+----+

Below my current steps but getting not expected output.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"B1", type text}, {"B2", Int64.Type}, {"B3", Int64.Type}, {"B4", type text}, {"B5", Int64.Type}}),
    #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Changed Type", {{"B5", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(#"Changed Type", {{"B5", type text}}, "en-US")[B5]), "B5", "B1")
in
    #"Pivoted Column"

Please some help on this. Thanks in advance.

Storax Storax · Accepted Answer · 2020-11-14T08:06:57

Do you mean something like that

let
    Source = Excel.CurrentWorkbook(){[Name="tblData"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"B1", type text}, {"B2", Int64.Type}, {"B3", Int64.Type}, {"B4", type text}, {"B5", Int64.Type}}),
    #"Cleaned Text" = Table.TransformColumns(#"Changed Type",{{"B1", Text.Clean, type text}, {"B4", Text.Clean, type text}}),
    #"Removed Columns" = Table.RemoveColumns(#"Cleaned Text",{"B1", "B2", "B4"}),
    #"Grouped Rows" = Table.Group(#"Removed Columns", {"B5"}, {{"tblB3", each _, type table [B3=nullable number, B5=nullable number]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "lstB3", each Table.Column([tblB3],"B3")),
    #"Removed Columns1" = Table.RemoveColumns(#"Added Custom",{"tblB3"}),
    #"Extracted Values" = Table.TransformColumns(#"Removed Columns1", {"lstB3", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Extracted Values", "lstB3", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"lstB3.1", "lstB3.2", "lstB3.3", "lstB3.4"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"lstB3.1", Int64.Type}, {"lstB3.2", Int64.Type}, {"lstB3.3", Int64.Type}, {"lstB3.4", Int64.Type}})
in
    #"Changed Type1"

That's the output I get

The M-code above has the disadvantage that it will not generate the correct number of columns if you add or remove something to the data.

If you look at the data set

the output is

but it should be

This is becasue the query above only considers the number of delimiters when the query was created.

With the following two queries one can fix the issue. The following query will prepare the data (define it as connection only)

let
    Source = Excel.CurrentWorkbook(){[Name="tblData"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"B1", type text}, {"B2", Int64.Type}, {"B3", Int64.Type}, {"B4", type text}, {"B5", Int64.Type}}),
    #"Cleaned Text" = Table.TransformColumns(#"Changed Type",{{"B1", Text.Clean, type text}, {"B4", Text.Clean, type text}}),
    #"Removed Columns" = Table.RemoveColumns(#"Cleaned Text",{"B1", "B2", "B4"}),
    #"Grouped Rows" = Table.Group(#"Removed Columns", {"B5"}, {{"tblB3", each _, type table [B3=nullable number, B5=nullable number]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "B3", each Table.Column([tblB3],"B3")),
    #"Removed Columns1" = Table.RemoveColumns(#"Added Custom",{"tblB3"}),
    tblToSplit = Table.TransformColumns(#"Removed Columns1", {"B3", each Text.Combine(List.Transform(_, Text.From), ","), type text})
in
    tblToSplit

The seconnd query based on the code here will dynamically split the column

let
  Source = tblSplit,
  DynamicColumnList = List.Transform({
    1..List.Max(
      Table.AddColumn(Source, "Custom", each List.Count(
        Text.PositionOfAny([B3], {","}, Occurrence.All)
      ))[Custom]
    ) + 1
  }, each "B3." & Text.From(_)),
  #"Split Column by Delimiter" = Table.SplitColumn(
    Source, 
    "B3", 
    Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), 
    DynamicColumnList
  )
in
  #"Split Column by Delimiter"

Transpose values in column based on key value in other column

1 Answers