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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

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

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors