Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
hbirk
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:

hbirk_0-1657106426772.png

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

hbirk_1-1657106471144.png

Is this possible?

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

What I have:

DateProduct A LowProduct A HighProduct A AverageProduct B LowProduct B HighProduct B AverageProduct C LowProduct C HighProduct 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:

DateLow bidHigh BidAverageProduct
2018-10-01 $  150,000.00 $  200,000.00 $         175,000.00A
2018-11-01 $  150,000.00 $  200,000.00 $         175,000.00A
2018-12-01 $  155,000.00 $  200,000.00 $         177,500.00A
2019-01-01 $  160,000.00 $  210,000.00 $         185,000.00A
2019-02-01 $  160,000.00 $  210,000.00 $         185,000.00A
2019-03-01 $  170,000.00 $  210,000.00 $         190,000.00A
2019-04-01 $  170,000.00 $  210,000.00 $         190,000.00A
2019-05-01 $  180,000.00 $  220,000.00 $         200,000.00A
2018-10-01 $  140,000.00 $  185,000.00 $         162,500.00B
2018-11-01 $  140,000.00 $  185,000.00 $         162,500.00B
2018-12-01 $  140,000.00 $  185,000.00 $         162,500.00B
2019-01-01 $  130,000.00 $  185,000.00 $         157,500.00B
2019-02-01 $  145,000.00 $  185,000.00 $         165,000.00B
2019-03-01 $  145,000.00 $  185,000.00 $         165,000.00B
2019-04-01 $  145,000.00 $  185,000.00 $         165,000.00B
2019-05-01 $  150,000.00 $  190,000.00 $         170,000.00B
2018-10-01 $  110,000.00 $  140,000.00 $         125,000.00C
2018-11-01 $  110,000.00 $  140,000.00 $         125,000.00C
2018-12-01 $  110,000.00 $  140,000.00 $         125,000.00C
2019-01-01 $  110,000.00 $  140,000.00 $         125,000.00C
2019-02-01 $  115,000.00 $  140,000.00 $         127,500.00C
2019-03-01 $  120,000.00 $  170,000.00 $         145,000.00C
2019-04-01 $  120,000.00 $  170,000.00 $         145,000.00C
2019-05-01 $  120,000.00 $  170,000.00 $         145,000.00C
1 ACCEPTED SOLUTION

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),
    #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table", [PromoteAllScalars=true]),
    #"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"} )),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Value"}),
    #"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"

 

Hope this answer solves your problem!
If you need any additional help please @ me in your reply.
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

View solution in original post

4 REPLIES 4
goncalogeraldes
Super User
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),
    #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table", [PromoteAllScalars=true]),
    #"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"

 

Hope this answer solves your problem!
If you need any additional help please @ me in your reply.
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

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:

hbirk_0-1657109915058.png

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

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),
    #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table", [PromoteAllScalars=true]),
    #"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"} )),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Value"}),
    #"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"

 

Hope this answer solves your problem!
If you need any additional help please @ me in your reply.
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

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

Helpful resources

Announcements
March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors