CODEWITHSUNDEEP
editorpowerbi-desktop
Amit Chavan
Amit Chavan

Reputation: 1

how to change single column to multiple columns with values in power query editor

How to change single column to multiple columns with values in power query editor?

Below is the input table in power query editor: enter image description here

Expected output: enter image description here

Tried using Pivot Columns and Transpose but error appearing and output not coming as expected.

Will be helpful if someone guide. Thanks!

Below is the input table in power query editor: enter image description here

Expected output: enter image description here

Tried using Pivot Columns and Transpose but error appearing and output not coming as expected.

Will be helpful if someone guide. Thanks!

Upvotes: 0

Views: 29

Answers (1)

Sam Nseir
Sam Nseir

Reputation: 12111

Try the following:

  1. Fill Down Emp ID and Name.
  2. Group by Emp ID and retains Rows.
  3. Add Index column to the grouped nested table.
  4. Add new column for "Skill " & Index.
  5. Expand the nested table.
  6. Pivot on "ColumnName" with "Skills" as value.
let
  Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
  #"Filled Down" = Table.FillDown(Source,{"Emp ID", "Name"}),
  #"Grouped Rows" = Table.Group(#"Filled Down", {"Emp ID"}, {{"Rows", each _, type table [Emp ID=nullable number, Name=nullable text, Skills=nullable text]}}),
  #"Added Index" = Table.TransformColumns(#"Grouped Rows", {"Rows", each Table.AddIndexColumn(_, "Index", 1, 1, Int64.Type)}),
  #"Added ColumnName" = Table.TransformColumns(#"Added Index", {"Rows", each Table.AddColumn(_, "ColumnName", each "Skill " & Number.ToText([Index]), type text)}),
  #"Expanded Rows" = Table.ExpandTableColumn(#"Added ColumnName", "Rows", {"Name", "Skills", "ColumnName"}, {"Name", "Skills", "ColumnName"}),
  #"Pivoted Column" = Table.Pivot(#"Expanded Rows", List.Distinct(#"Expanded Rows"[ColumnName]), "ColumnName", "Skills")
in
  #"Pivoted Column"

Upvotes: 0

Related Questions