Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi all,
Does anyone have advice on how to go about pivoting some data? My current data looks like this:
Order No | Line Item | Index |
0001 | Red Book | 1 |
0002 | Blue Book | 2 |
0002 | Green Book | 3 |
0003 | Blue Book | 4 |
0004 | Yellow Book | 5 |
0004 | Purple Book | 6 |
0004 | Green Book | 7 |
0004 | Blue Book | 8 |
What I'm trying to pivot to is this structure:
Order No | Line Item 1 | Line Item 2 | Line Item 3 | Line Item 4 |
0001 | Red Book | |||
0002 | Blue Book | Green Book | ||
0003 | Blue Book | |||
0004 | Yellow Book | Purple Book | Green Book | Blue Book |
My problem is that there are varying numbers of Line Items per Order, so how do I create a column in the source data to use as the "Values column" to create the new columns?
Thanks in advance for your help 🙂
Richard
Solved! Go to Solution.
@RichardP Please try this in Power Query Editor
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjAwMFTSUQpKTVFwys/PBjINlWJ1wOJGQI5TTmkqTMIIWcK9KDU1DyZjDJMxRtNiApMwAXIiU3Ny8sthUqbIUgGlRQU5cF1myFIoFpkjyyBbZKEUGwsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Order No" = _t, LineItem = _t, Index = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Order No", Int64.Type}, {"LineItem", type text}, {"Index", Int64.Type}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"Order No"}, {{"AllRows", each _, type table}}), #"Added Custom" = Table.AddColumn(#"Grouped Rows", "LineItem", each [AllRows][LineItem]), #"Extracted Values" = Table.TransformColumns(#"Added Custom", {"LineItem", each Text.Combine(List.Transform(_, Text.From), ","), type text}), #"Split Column by Delimiter" = Table.SplitColumn(#"Extracted Values", "LineItem", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"LineItem.1", "LineItem.2", "LineItem.3", "LineItem.4"}), #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"LineItem.1", type text}, {"LineItem.2", type text}, {"LineItem.3", type text}, {"LineItem.4", type text}}), #"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"AllRows"}) in #"Removed Columns"
For Reference, Here is the overview of the steps that are implemented as above.
Proud to be a PBI Community Champion
@RichardP Please try this in Power Query Editor
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjAwMFTSUQpKTVFwys/PBjINlWJ1wOJGQI5TTmkqTMIIWcK9KDU1DyZjDJMxRtNiApMwAXIiU3Ny8sthUqbIUgGlRQU5cF1myFIoFpkjyyBbZKEUGwsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Order No" = _t, LineItem = _t, Index = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Order No", Int64.Type}, {"LineItem", type text}, {"Index", Int64.Type}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"Order No"}, {{"AllRows", each _, type table}}), #"Added Custom" = Table.AddColumn(#"Grouped Rows", "LineItem", each [AllRows][LineItem]), #"Extracted Values" = Table.TransformColumns(#"Added Custom", {"LineItem", each Text.Combine(List.Transform(_, Text.From), ","), type text}), #"Split Column by Delimiter" = Table.SplitColumn(#"Extracted Values", "LineItem", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"LineItem.1", "LineItem.2", "LineItem.3", "LineItem.4"}), #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"LineItem.1", type text}, {"LineItem.2", type text}, {"LineItem.3", type text}, {"LineItem.4", type text}}), #"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"AllRows"}) in #"Removed Columns"
For Reference, Here is the overview of the steps that are implemented as above.
Proud to be a PBI Community Champion
another way using the pivot function with an appropriate aggregation function
let
Source = Table.FromRows(
Json.Document(
Binary.Decompress(
Binary.FromText(
"i45WMjAwMFTSUQpKTVFwys/PBjINlWJ1wOJGQI5TTmkqTMIIWcK9KDU1DyZjDJMxRtNiApMwAXIiU3Ny8sthUqbIUgGlRQU5cF1myFIoFpkjyyBbZKEUGwsA",
BinaryEncoding.Base64
),
Compression.Deflate
)
),
let
_t = ((type text) meta [Serialized.Text = true])
in
type table[#"Order No" = _t, #"Line Item" = _t, Index = _t]
),
#"Changed Type" = Table.TransformColumnTypes(
Source,
{{"Order No", Int64.Type}, {"Line Item", type text}, {"Index", Int64.Type}}
),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type", {"Index"}),
#"Pivoted Column" = Table.Pivot(
Table.TransformColumnTypes(#"Removed Columns", {{"Order No", type text}}, "it-IT"),
List.Distinct(
Table.TransformColumnTypes(#"Removed Columns", {{"Order No", type text}}, "it-IT")[#"Order No"]
),
"Order No",
"Line Item",
(t) => Text.Combine(t, "#")
),
#"Demoted Headers" = Table.DemoteHeaders(#"Pivoted Column"),
#"Transposed Table" = Table.Transpose(#"Demoted Headers"),
#"Split Column by Delimiter" = Table.SplitColumn(
#"Transposed Table",
"Column2",
Splitter.SplitTextByDelimiter("#", QuoteStyle.Csv),
{"Column2.1", "Column2.2", "Column2.3", "Column2.4"}
)
in
#"Split Column by Delimiter"
Try searching for ranking within a group to get you started
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
99 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
102 | |
88 | |
63 |