Power BI | Power Query: how to create multiple columns from one single value column

Question

I am currently using Power BI (Power Query) to clean up a dataset, and I am having some problems unpivotting a table the right way (see image below). Any suggestions on how to sort this out?

link to image

For reference, the Microsoft Docs page on how to add an index column contains a step-by-step tutorial on how to pivot a table similar to yours. In your case, you already have the integer-divide column and only need to add a modulo column (based on an index column) as suggested by Marcus. — Patrick FitzGerald
– Patrick FitzGerald, Commented Sep 9, 2021 at 17:21

Marcus · Accepted Answer · 2021-09-06 14:05:41Z

1

For unpivoting to work you need three columns:

Key	Column	Value
1	A	1.3
1	B	3
1	C	New
2	A	2.3
2	B	3
2	C	Old

So this could be unpivoted to:

Key	Column A	Column B	Column C
1	1.3	3	New
2	2.3	3	Old

So for this you would need to add a column-column where the row value is the column name - prior to unpivoting the table.

Edit:

If your rows are ordered according to columns per key, you can do something like this, where you create your own column-column prior to pivoting. See code you can paste into a blank query:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Tcq7DQAgCAXAXV5tI0zAZwvC/muYaCSvu+KqsLFgZuj17O7jiBhn5rXQF/pCX+grfaWv9PX/Pg==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [id = _t, values = _t]),
    #"Added Index" = Table.AddIndexColumn(Source, "Index", 1, 1, Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Added Index", "Custom", each Number.Mod([Index],4)),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Index"}),
    #"Pivoted Column1" = Table.Pivot(Table.TransformColumnTypes(#"Removed Columns", {{"Custom", type text}}, "nb-NO"), List.Distinct(Table.TransformColumnTypes(#"Removed Columns", {{"Custom", type text}}, "nb-NO")[Custom]), "Custom", "values")
in
    #"Pivoted Column1"

edited Sep 6, 2021 at 14:05

answered Sep 6, 2021 at 13:42

Marcus

4,0251 gold badge7 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

avg_joe Over a year ago

Thanks, Marcus! I might have used the word "unpivotting" inappropriately here as I don't have any extra column where the row value is the column name. Given the situation as described in the linked image, is there any feasible solution to the problem?

Marcus Over a year ago

Well, you have to add a column that describes the column name. You can't do what you want by letting the computer "smell" what column is appropriate, you need to tell it what values go in which column - I assume you don't have AAA as actual values. See my edit.

avg_joe Over a year ago

Thanks, Marcus! I will give it a shot and mark this answer as useful.

Collectives™ on Stack Overflow

Power BI | Power Query: how to create multiple columns from one single value column

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related