Pivot triple pairs of values

[hbirk]

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

[goncalogeraldes]

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

[hbirk]

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

[goncalogeraldes · 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

[hbirk]

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