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

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.

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
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

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

Top Solution Authors
Top Kudoed Authors