cancel
Showing results for
Did you mean:
Frequent Visitor

## Pivot triple pairs of values

Hi!

I have a table with entries for high, low and average bids for three products. It looks like this:

And i want to group the high, low and average bids into the same row so the table looks like this:

Is this possible?

I can send the excel data as dm, otherwise here is the data provided as tables:

What I have:

 Date Product A Low Product A High Product A Average Product B Low Product B High Product B Average Product C Low Product C High Product C Average 2018-10-01 \$  150,000.00 \$  200,000.00 \$         175,000.00 \$  140,000.00 \$  185,000.00 \$         162,500.00 \$  110,000.00 \$  140,000.00 \$         125,000.00 2018-11-01 \$  150,000.00 \$  200,000.00 \$         175,000.00 \$  140,000.00 \$  185,000.00 \$         162,500.00 \$  110,000.00 \$  140,000.00 \$         125,000.00 2018-12-01 \$  155,000.00 \$  200,000.00 \$         177,500.00 \$  140,000.00 \$  185,000.00 \$         162,500.00 \$  110,000.00 \$  140,000.00 \$         125,000.00 2019-01-01 \$  160,000.00 \$  210,000.00 \$         185,000.00 \$  130,000.00 \$  185,000.00 \$         157,500.00 \$  110,000.00 \$  140,000.00 \$         125,000.00 2019-02-01 \$  160,000.00 \$  210,000.00 \$         185,000.00 \$  145,000.00 \$  185,000.00 \$         165,000.00 \$  115,000.00 \$  140,000.00 \$         127,500.00 2019-03-01 \$  170,000.00 \$  210,000.00 \$         190,000.00 \$  145,000.00 \$  185,000.00 \$         165,000.00 \$  120,000.00 \$  170,000.00 \$         145,000.00 2019-04-01 \$  170,000.00 \$  210,000.00 \$         190,000.00 \$  145,000.00 \$  185,000.00 \$         165,000.00 \$  120,000.00 \$  170,000.00 \$         145,000.00 2019-05-01 \$  180,000.00 \$  220,000.00 \$         200,000.00 \$  150,000.00 \$  190,000.00 \$         170,000.00 \$  120,000.00 \$  170,000.00 \$         145,000.00

What I want:

 Date Low bid High Bid Average Product 2018-10-01 \$  150,000.00 \$  200,000.00 \$         175,000.00 A 2018-11-01 \$  150,000.00 \$  200,000.00 \$         175,000.00 A 2018-12-01 \$  155,000.00 \$  200,000.00 \$         177,500.00 A 2019-01-01 \$  160,000.00 \$  210,000.00 \$         185,000.00 A 2019-02-01 \$  160,000.00 \$  210,000.00 \$         185,000.00 A 2019-03-01 \$  170,000.00 \$  210,000.00 \$         190,000.00 A 2019-04-01 \$  170,000.00 \$  210,000.00 \$         190,000.00 A 2019-05-01 \$  180,000.00 \$  220,000.00 \$         200,000.00 A 2018-10-01 \$  140,000.00 \$  185,000.00 \$         162,500.00 B 2018-11-01 \$  140,000.00 \$  185,000.00 \$         162,500.00 B 2018-12-01 \$  140,000.00 \$  185,000.00 \$         162,500.00 B 2019-01-01 \$  130,000.00 \$  185,000.00 \$         157,500.00 B 2019-02-01 \$  145,000.00 \$  185,000.00 \$         165,000.00 B 2019-03-01 \$  145,000.00 \$  185,000.00 \$         165,000.00 B 2019-04-01 \$  145,000.00 \$  185,000.00 \$         165,000.00 B 2019-05-01 \$  150,000.00 \$  190,000.00 \$         170,000.00 B 2018-10-01 \$  110,000.00 \$  140,000.00 \$         125,000.00 C 2018-11-01 \$  110,000.00 \$  140,000.00 \$         125,000.00 C 2018-12-01 \$  110,000.00 \$  140,000.00 \$         125,000.00 C 2019-01-01 \$  110,000.00 \$  140,000.00 \$         125,000.00 C 2019-02-01 \$  115,000.00 \$  140,000.00 \$         127,500.00 C 2019-03-01 \$  120,000.00 \$  170,000.00 \$         145,000.00 C 2019-04-01 \$  120,000.00 \$  170,000.00 \$         145,000.00 C 2019-05-01 \$  120,000.00 \$  170,000.00 \$         145,000.00 C
1 ACCEPTED SOLUTION
Super User

I have changed the query to match your needs. Check the .pbix in attachment if you need:

``````let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("1ZQ9D4IwEIb/SkMcwdwVyscIODg4uBMHokSdSAjq35cBSnnBSNTE0I3LPW+eu7RkmbXJ68KyrX1Vnm7HWsRiVz4G39vr+TIoxPeiys8mlACUIJRMQClAKUKphg52Zkni0GFyiJsWsRKCFdlEtCZqC5Kg0B4O1LDOHjRyqKZJX9pq0MhIYlRHSp3Yy/OS5aUpr+bKB6DwD/moMTfkfdw8JndBqMbuXHmFY38lL38i72Hh5eaxkd/dQC2vx+7lXUM+mCsfjdb1sbzEKJToSG9i896S5ZUhH6I8Jrdn9JBH/6nRePqpY+Mn8ocn", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"(blank)" = _t, #"(blank).1" = _t, #"(blank).2" = _t, #"(blank).3" = _t, #"(blank).4" = _t, #"(blank).5" = _t, #"(blank).6" = _t, #"(blank).7" = _t, #"(blank).8" = _t, #"(blank).9" = _t]),
#"Transposed Table" = Table.Transpose(Source),
#"Split Column by Delimiter" = Table.SplitColumn(#"Promoted Headers", "Date", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, true), {"Date.1", "Date.2"}),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Split Column by Delimiter", {"Date.1", "Date.2"}, "Attribute", "Value"),
#"Added Custom" = Table.AddColumn(#"Unpivoted Other Columns", "Custom", each Text.Select ( [Value], {"0".."9"} )),
#"Split Column by Positions" = Table.SplitColumn(#"Removed Columns", "Custom", Splitter.SplitTextByPositions({0,2}, true), {"Bid Value"}),
#"Pivoted Column" = Table.Pivot(#"Split Column by Positions", List.Distinct(#"Split Column by Positions"[Date.2]), "Date.2", "Bid Value"),
#"Reordered Columns" = Table.ReorderColumns(#"Pivoted Column",{"Attribute", "Low", "High", "Average", "Date.1"}),
#"Renamed Columns" = Table.RenameColumns(#"Reordered Columns",{{"Attribute", "Date"}, {"Date.1", "Product"}}),
#"Extracted Last Characters" = Table.TransformColumns(#"Renamed Columns", {{"Product", each Text.End(_, 1), type text}}),
#"Changed Type" = Table.TransformColumnTypes(#"Extracted Last Characters",{{"Low", Currency.Type}, {"High", Currency.Type}, {"Average", Currency.Type}, {"Date", type date}})
in
#"Changed Type"``````

If my reply provided you with a solution, please consider marking it as a solution ✔️ or giving it a kudoe 👍
Thanks!

You can also check out my LinkedIn!

Best regards,
Gonçalo Geraldes

4 REPLIES 4
Super User

Hello there @hbirk ! Please in the attached .pbix the solution for your request.

``````let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("1ZQ9D4IwEIb/SkMcwdwVyscIODg4uBMHokSdSAjq35cBSnnBSNTE0I3LPW+eu7RkmbXJ68KyrX1Vnm7HWsRiVz4G39vr+TIoxPeiys8mlACUIJRMQClAKUKphg52Zkni0GFyiJsWsRKCFdlEtCZqC5Kg0B4O1LDOHjRyqKZJX9pq0MhIYlRHSp3Yy/OS5aUpr+bKB6DwD/moMTfkfdw8JndBqMbuXHmFY38lL38i72Hh5eaxkd/dQC2vx+7lXUM+mCsfjdb1sbzEKJToSG9i896S5ZUhH6I8Jrdn9JBH/6nRePqpY+Mn8ocn", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"(blank)" = _t, #"(blank).1" = _t, #"(blank).2" = _t, #"(blank).3" = _t, #"(blank).4" = _t, #"(blank).5" = _t, #"(blank).6" = _t, #"(blank).7" = _t, #"(blank).8" = _t, #"(blank).9" = _t]),
#"Transposed Table" = Table.Transpose(Source),
#"Split Column by Delimiter" = Table.SplitColumn(#"Promoted Headers", "Date", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, true), {"Date.1", "Date.2"}),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Split Column by Delimiter", {"Date.1", "Date.2"}, "Attribute", "Value"),
#"Renamed Columns" = Table.RenameColumns(#"Unpivoted Other Columns",{{"Date.1", "Product"}, {"Date.2", "Bid Type"}, {"Attribute", "Date"}, {"Value", "Bid Value"}}),
#"Added Custom" = Table.AddColumn(#"Renamed Columns", "Custom", each Text.Select ( [Bid Value], {"0".."9"} )),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Bid Value"}),
#"Split Column by Positions" = Table.SplitColumn(#"Removed Columns", "Custom", Splitter.SplitTextByPositions({0,2}, true), {"Bid Value"}),
#"Changed Type" = Table.TransformColumnTypes(#"Split Column by Positions",{{"Date", type date}, {"Bid Value", Currency.Type}})
in
#"Changed Type"``````

If my reply provided you with a solution, please consider marking it as a solution ✔️ or giving it a kudoe 👍
Thanks!

You can also check out my LinkedIn!

Best regards,
Gonçalo Geraldes

Frequent Visitor

Thank you, but what I am looking for is pairing the low, high and average bids together in each row, so that i get rows like these:

I need them in the same row, as I have to provide a bidding range that consists of product A: 150k - 200k, and have that value tagged to product A itself

Super User

I have changed the query to match your needs. Check the .pbix in attachment if you need:

``````let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("1ZQ9D4IwEIb/SkMcwdwVyscIODg4uBMHokSdSAjq35cBSnnBSNTE0I3LPW+eu7RkmbXJ68KyrX1Vnm7HWsRiVz4G39vr+TIoxPeiys8mlACUIJRMQClAKUKphg52Zkni0GFyiJsWsRKCFdlEtCZqC5Kg0B4O1LDOHjRyqKZJX9pq0MhIYlRHSp3Yy/OS5aUpr+bKB6DwD/moMTfkfdw8JndBqMbuXHmFY38lL38i72Hh5eaxkd/dQC2vx+7lXUM+mCsfjdb1sbzEKJToSG9i896S5ZUhH6I8Jrdn9JBH/6nRePqpY+Mn8ocn", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"(blank)" = _t, #"(blank).1" = _t, #"(blank).2" = _t, #"(blank).3" = _t, #"(blank).4" = _t, #"(blank).5" = _t, #"(blank).6" = _t, #"(blank).7" = _t, #"(blank).8" = _t, #"(blank).9" = _t]),
#"Transposed Table" = Table.Transpose(Source),
#"Split Column by Delimiter" = Table.SplitColumn(#"Promoted Headers", "Date", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, true), {"Date.1", "Date.2"}),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Split Column by Delimiter", {"Date.1", "Date.2"}, "Attribute", "Value"),
#"Added Custom" = Table.AddColumn(#"Unpivoted Other Columns", "Custom", each Text.Select ( [Value], {"0".."9"} )),
#"Split Column by Positions" = Table.SplitColumn(#"Removed Columns", "Custom", Splitter.SplitTextByPositions({0,2}, true), {"Bid Value"}),
#"Pivoted Column" = Table.Pivot(#"Split Column by Positions", List.Distinct(#"Split Column by Positions"[Date.2]), "Date.2", "Bid Value"),
#"Reordered Columns" = Table.ReorderColumns(#"Pivoted Column",{"Attribute", "Low", "High", "Average", "Date.1"}),
#"Renamed Columns" = Table.RenameColumns(#"Reordered Columns",{{"Attribute", "Date"}, {"Date.1", "Product"}}),
#"Extracted Last Characters" = Table.TransformColumns(#"Renamed Columns", {{"Product", each Text.End(_, 1), type text}}),
#"Changed Type" = Table.TransformColumnTypes(#"Extracted Last Characters",{{"Low", Currency.Type}, {"High", Currency.Type}, {"Average", Currency.Type}, {"Date", type date}})
in
#"Changed Type"``````

If my reply provided you with a solution, please consider marking it as a solution ✔️ or giving it a kudoe 👍
Thanks!

You can also check out my LinkedIn!

Best regards,
Gonçalo Geraldes

Frequent Visitor

Thank you! This was exactly what I was looking for!

Announcements

#### Power BI T-Shirt Design Challenge 2023

Vote for your favorite t-shirt design now through March 28.

#### Power BI March 2023 Update

Find out more about the March 2023 update.

#### March 2023 Events

Find out more about the online and in person events happening in March!

Top Solution Authors
Top Kudoed Authors