Split a column by rows

let Source = Excel.CurrentWorkbook(){[Name="UserData"]}[Content], #"Added Index" = Table.AddIndexColumn(Source, "Index", 0, 1), #"Inserted Modulo" = Table.AddColumn(#"Added Index", "Modulo", each Number.Mod([Index], 2), type number), #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Inserted Modulo", {{"Modulo", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(#"Inserted Modulo", {{"Modulo", type text}}, "en-US")[Modulo]), "Modulo", "UserName"), #"Filled Down" = Table.FillDown(#"Pivoted Column",{"0"}), #"Filtered Rows" = Table.SelectRows(#"Filled Down", each ([1] <> null)), #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Index"}), #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"0", "UserName"}, {"1", "Email Address"}}) in #"Renamed Columns"

Lor3nzo

Copper Contributor

Mar 13, 2021

Subodh_Tiwari_sktneer

I'm stuck on a similar issue of @Stephen_Kane.

I'm new here in the Community and I hope that Subodh_Tiwari_sktneer or someone else can help me to come up with a solution.

I have 3 columns as follow:

Order ID	Attributes	Values
123	Quantity	1
123	Amount	$50
123	Product Name	Test Product 1
123	Category	Test Category 1
123	Quantity	2
123	Amount	$100
123	Product Name	Test Product 2
123	Category	Test Category 2
234	Quantity	1
234	Amount	$70
234	Product Name	Test Product 3
234	Category	Test Category 3
234	Quantity	1
234	Amount	$100
234	Product Name	Test Product 1
234	Category	Test Category 1

As you can see I have duplicate ATTRIBUTES and different type of VALUES (numbers, currencies and text).

I need to split each single attribute in columns with the corresponding value below.

Thank you in advance for helping me with this!

Subodh_Tiwari_sktneer

Silver Contributor

Mar 14, 2021

Lor3nzo

Ideally you should have opened your own question Anyways follow the steps given below...

Convert your source data into an Excel Table and rename it as "Raw_Data".
Create a Blank Query, open the Advanced Editor and replace the existing content with the query given below.
On Home Tab, click on Close & Load to and choose the destination for the output table.

let
    Source = Excel.CurrentWorkbook(){[Name="Raw_Data"]}[Content],
    #"Added Index" = Table.AddIndexColumn(Source, "Index", 0, 1, Int64.Type),
    #"Inserted Modulo" = Table.AddColumn(#"Added Index", "Modulo", each Number.Mod([Index], 4), type number),
    #"Pivoted Column" = Table.Pivot(#"Inserted Modulo", List.Distinct(#"Inserted Modulo"[Attributes]), "Attributes", "Values"),
    #"Filled Up" = Table.FillUp(#"Pivoted Column",{"Quantity", "Amount", "Product Name", "Category"}),
    #"Filtered Rows" = Table.SelectRows(#"Filled Up", each ([Modulo] = 0)),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Index", "Modulo"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Removed Columns",{{"Order ID", Int64.Type}, {"Quantity", Int64.Type}, {"Amount", Currency.Type}, {"Product Name", type text}, {"Category", type text}})
in
    #"Changed Type"

Lor3nzo
Copper Contributor
Mar 14, 2021
Subodh_Tiwari_sktneer thank you very much for your kind reply.

It works perfectly!
Next time I will follow your advice.

Thank you again

Forum Discussion

Resources