Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
I am trying to take the following table and create a new grouped column with new columns from the 2nd column.
PriceIndexNumber | CompositeContractNumber |
CMAA 2/3;1/3 MD/CUSH | 2/3;1/3 FIXED ROLL |
CMAA 2/3;1/3 MD/CUSH | ARGUS MID/CUSH DIFF |
CMAA 2/3;1/3 MD/CUSH | NYMEX-CMA-A |
CMAA 2/3;1/3 WTI/WTS | 2/3;1/3 FIXED ROLL |
CMAA 2/3;1/3 WTI/WTS | ARGUS WTI/WTS |
CMAA 2/3;1/3 WTI/WTS | NYMEX-CMA-A |
CMAA ACTUAL MID/CUSH | 7 DAY ROLL |
CMAA ACTUAL MID/CUSH | ARGUS MID/CUSH DIFF |
CMAA ACTUAL MID/CUSH | NYMEX-CMA-A |
CMAA ACTUAL WTI/WTS | 7 DAY ROLL |
CMAA ACTUAL WTI/WTS | ARGUS WTI/WTS |
CMAA ACTUAL WTI/WTS | NYMEX-CMA-A |
CMAT CMA MID/CUSH | ARGUS CMA DIFF |
CMAT CMA MID/CUSH | ARGUS MID/CUSH DIFF |
CMAT CMA MID/CUSH | NYMEX CMA-T |
CMAT TRADE MID/CUSH | ACT-TRADE DAYS ROLL |
CMAT TRADE MID/CUSH | ARGUS MID/CUSH DIFF |
CMAT TRADE MID/CUSH | NYMEX CMA-T |
ENT,ARGP+ | ARGUS P PLUS |
ENT,ARGP+ | ENT WTI |
MIDLAND NYMEX SOUR | 7 DAY ROLL |
MIDLAND NYMEX SOUR | ARGUS WTI/WTS |
MIDLAND NYMEX SOUR | NYMEX-CMA-A |
NX,SPROLL,MIDCUSHDIF | 2/3;1/3 FIXED ROLL |
NX,SPROLL,MIDCUSHDIF | ARGUS MID/CUSH DIFF |
NX,SPROLL,MIDCUSHDIF | NYMEX-CMA-A |
Example, the output should now be:
ContractNumber | Price 1 | Price 2 | Price 3 |
CMAA 2/3;1/3 MD/CUSH | 2/3;1/3 FIXED ROLL | ARGUS MID/CUSH DIFF | NYMEX-CMA-A |
CMAA 2/3;1/3 WTI/WTS | 2/3;1/3 FIXED ROLL | ARGUS WTI/WTS | NYMEX-CMA-A |
CMAA ACTUAL MID/CUSH | 7 DAY ROLL | ARGUS MID/CUSH DIFF | NYMEX-CMA-A |
CMAA ACTUAL WTI/WTS | 7 DAY ROLL | ARGUS WTI/WTS | NYMEX-CMA-A |
CMAT CMA MID/CUSH | ARGUS CMA DIFF | ARGUS MID/CUSH DIFF | NYMEX CMA-T |
CMAT TRADE MID/CUSH | ACT-TRADE DAYS ROLL | ARGUS MID/CUSH DIFF | NYMEX CMA-T |
ENT,ARGP+ | ARGUS P PLUS | ENT WTI | |
MIDLAND NYMEX SOUR | 7 DAY ROLL | ARGUS WTI/WTS | NYMEX-CMA-A |
NX,SPROLL,MIDCUSHDIF | 2/3;1/3 FIXED ROLL | ARGUS MID/CUSH DIFF | NYMEX-CMA-A |
NYCAL,ACMADIF,AMIDCU | ARGUS CMA DIFF | ARGUS MID/CUSH DIFF | NYMEX-CMA-A |
NYCMA,ACMADIFF,AM/CD | ARGUS CMA DIFF | ARGUS MID/CUSH DIFF | NYMEX-CMA-A |
NYM+2/3;1/3+ARGWTI&S | 2/3;1/3 FIXED ROLL | ARGUS WTI/WTS | NYMEX-CMA-A |
Solved! Go to Solution.
This solution only works if you have 3 prices:
Import table with two columns and Add Index Column from 1 (Power Query Editor->Add column tab)
Right click Query name and Duplicate:
Select PriceIndexNumber column-> Right click it and choose Remove Duplicate -> Ready to pick Price 1
Go to Home tab and choose Merge queries -> Merge queries:
Expand selection
Make a second Duplicate of the main table. Select new duplicate and right click PriceIndexNumber and select Group by:
Select MaxIndex column and choose Insert Is Even from Add Column Tab
Then Add Custom column MaxIndexMinusOne
Then add Conditional column
Then Merge queries
and expand
Add another conditional column
and merge queries
and expand
Now merge Second and third queries base on priceindexnumber and expand. All set
The last 4 rows should be
#"Grouped Rows" = Table.Group(#"0OTMCOGPPRCD_Table", {"PriceIndexNumber"}, {{"Number", each Text.Combine( [CompositeContractNumber] , ", "), type nullable text}}),
#"Split Column by Delimiter" = Table.SplitColumn(#"Grouped Rows", "Number", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"No.1", "No.2", "No.3"})
in
#"Split Column by Delimiter"
There might be other issues with column names. see how you get on.
This solution only works if you have 3 prices:
Import table with two columns and Add Index Column from 1 (Power Query Editor->Add column tab)
Right click Query name and Duplicate:
Select PriceIndexNumber column-> Right click it and choose Remove Duplicate -> Ready to pick Price 1
Go to Home tab and choose Merge queries -> Merge queries:
Expand selection
Make a second Duplicate of the main table. Select new duplicate and right click PriceIndexNumber and select Group by:
Select MaxIndex column and choose Insert Is Even from Add Column Tab
Then Add Custom column MaxIndexMinusOne
Then add Conditional column
Then Merge queries
and expand
Add another conditional column
and merge queries
and expand
Now merge Second and third queries base on priceindexnumber and expand. All set
The last 4 rows should be
#"Grouped Rows" = Table.Group(#"0OTMCOGPPRCD_Table", {"PriceIndexNumber"}, {{"Number", each Text.Combine( [CompositeContractNumber] , ", "), type nullable text}}),
#"Split Column by Delimiter" = Table.SplitColumn(#"Grouped Rows", "Number", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"No.1", "No.2", "No.3"})
in
#"Split Column by Delimiter"
There might be other issues with column names. see how you get on.
WOW! That was amazing... I need to learn more about Advanced Editor. LIFE CHANGER @HotChilli
@rcharara it was a mammoth effort and very well explained. I feel a little bit bad about posting this.
Add these 2 lines to Advanced editor (correct any step names or column names)
#"Grouped Rows" = Table.Group(#"Changed Type", {"PriceIndexNumber"}, {{"Number", each Text.Combine( [CompositeContractNumber] , ", "), type nullable text}}),
#"Split Column by Delimiter" = Table.SplitColumn(#"Grouped Rows", "Number", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"No.1", "No.2", "No.3"})
Basically, it groups on PriceIndexNumber and concatenates the number values with a ",".
Then we split the column on ","
Thanks for the quick reply, I tried adding your advanced editor language but it did not work. See below.
I know it is me and not you!
let
Source = Odbc.DataSource("dsn=Wolfepack", [HierarchicalNavigation=true]),
#"Filtered Rows" = Table.SelectRows(Source, each ([Name] = "0OTMCOGPPRCD")),
#"0OTMCOGPPRCD_Table" = #"Filtered Rows"{[Name="0OTMCOGPPRCD",Kind="Table"]}[Data],
#"Grouped Rows" = Table.Group(#"Changed Type", {"PriceIndexNumber"}, {{"Number", each Text.Combine( [CompositeContractNumber] , ", "), type nullable text}}),
#"Split Column by Delimiter" = Table.SplitColumn(#"Grouped Rows", "Number", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"No.1", "No.2", "No.3"})
in
#"0OTMCOGPPRCD_Table"
Nothing happens when I add this, it does say no syntax errors have been dedected.
Best,
Austin
User | Count |
---|---|
54 | |
22 | |
19 | |
16 | |
11 |
User | Count |
---|---|
81 | |
55 | |
39 | |
20 | |
12 |