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

Join the Fabric FabCon Global Hackathon—running virtually through Nov 3. Open to all skill levels. $10,000 in prizes! Register now.

Reply
Anonymous
Not applicable

Power BI - Group Columns and Create new Columns

I am trying to take the following table and create a new grouped column with new columns from the 2nd column.

 

PriceIndexNumberCompositeContractNumber
CMAA 2/3;1/3 MD/CUSH  2/3;1/3 FIXED ROLL
CMAA 2/3;1/3 MD/CUSHARGUS MID/CUSH DIFF 
CMAA 2/3;1/3 MD/CUSHNYMEX-CMA-A         
CMAA 2/3;1/3 WTI/WTS  2/3;1/3 FIXED ROLL
CMAA 2/3;1/3 WTI/WTSARGUS WTI/WTS       
CMAA 2/3;1/3 WTI/WTSNYMEX-CMA-A         
CMAA ACTUAL MID/CUSH          7 DAY ROLL
CMAA ACTUAL MID/CUSHARGUS MID/CUSH DIFF 
CMAA ACTUAL MID/CUSHNYMEX-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,MIDCUSHDIFARGUS MID/CUSH DIFF 
NX,SPROLL,MIDCUSHDIFNYMEX-CMA-A         

 

Example, the output should now be:

 

ContractNumberPrice 1Price 2Price 3
CMAA 2/3;1/3 MD/CUSH  2/3;1/3 FIXED ROLLARGUS MID/CUSH DIFF NYMEX-CMA-A         
CMAA 2/3;1/3 WTI/WTS  2/3;1/3 FIXED ROLLARGUS WTI/WTS       NYMEX-CMA-A         
CMAA ACTUAL MID/CUSH          7 DAY ROLLARGUS MID/CUSH DIFF NYMEX-CMA-A         
CMAA ACTUAL WTI/WTS           7 DAY ROLLARGUS 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 ROLLARGUS WTI/WTS       NYMEX-CMA-A         
NX,SPROLL,MIDCUSHDIF  2/3;1/3 FIXED ROLLARGUS MID/CUSH DIFF NYMEX-CMA-A         
NYCAL,ACMADIF,AMIDCUARGUS CMA DIFF      ARGUS MID/CUSH DIFF NYMEX-CMA-A         
NYCMA,ACMADIFF,AM/CDARGUS CMA DIFF      ARGUS MID/CUSH DIFF NYMEX-CMA-A         
NYM+2/3;1/3+ARGWTI&S  2/3;1/3 FIXED ROLLARGUS WTI/WTS       NYMEX-CMA-A         

 

2 ACCEPTED SOLUTIONS
rcharara
Regular Visitor

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)

rcharara_0-1602098891602.png

Right click Query name and Duplicate:

Select PriceIndexNumber column-> Right click it and choose Remove Duplicate -> Ready to pick Price 1

rcharara_1-1602099065715.png

Go to Home tab and choose Merge queries -> Merge queries:

rcharara_2-1602099213443.png

Expand selection

rcharara_3-1602099426670.png

rcharara_4-1602099471165.png

Make a second Duplicate of the main table. Select new duplicate and right click PriceIndexNumber and select Group by:

rcharara_6-1602100106030.png

Select MaxIndex column and choose Insert Is Even from Add Column Tab

Then Add Custom column MaxIndexMinusOne

rcharara_7-1602100287493.png

Then add Conditional column 

rcharara_8-1602100341714.png

Then Merge queries

rcharara_9-1602100415451.png

 

and expand 

rcharara_10-1602100472289.png

 

Add another conditional column

rcharara_11-1602100529777.png

and merge queries

rcharara_12-1602100598817.png

and expand

rcharara_13-1602100641945.png

 

Now merge Second and third queries base on priceindexnumber and expand. All set

rcharara_14-1602100712249.png

 

 

 

 

 

 

 

 

 

 

 

 

 

View solution in original post

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. 

View solution in original post

6 REPLIES 6
rcharara
Regular Visitor

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)

rcharara_0-1602098891602.png

Right click Query name and Duplicate:

Select PriceIndexNumber column-> Right click it and choose Remove Duplicate -> Ready to pick Price 1

rcharara_1-1602099065715.png

Go to Home tab and choose Merge queries -> Merge queries:

rcharara_2-1602099213443.png

Expand selection

rcharara_3-1602099426670.png

rcharara_4-1602099471165.png

Make a second Duplicate of the main table. Select new duplicate and right click PriceIndexNumber and select Group by:

rcharara_6-1602100106030.png

Select MaxIndex column and choose Insert Is Even from Add Column Tab

Then Add Custom column MaxIndexMinusOne

rcharara_7-1602100287493.png

Then add Conditional column 

rcharara_8-1602100341714.png

Then Merge queries

rcharara_9-1602100415451.png

 

and expand 

rcharara_10-1602100472289.png

 

Add another conditional column

rcharara_11-1602100529777.png

and merge queries

rcharara_12-1602100598817.png

and expand

rcharara_13-1602100641945.png

 

Now merge Second and third queries base on priceindexnumber and expand. All set

rcharara_14-1602100712249.png

 

 

 

 

 

 

 

 

 

 

 

 

 

Anonymous
Not applicable

@rcharara 

 

This worked great! Thanks for all the effort you put in! I really appreciate it 🙂

 

Austin

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. 

Anonymous
Not applicable

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.

 

@Anonymous 

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 ","

Anonymous
Not applicable

@HotChilli 

 

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

Helpful resources

Announcements
FabCon Global Hackathon Carousel

FabCon Global Hackathon

Join the Fabric FabCon Global Hackathon—running virtually through Nov 3. Open to all skill levels. $10,000 in prizes!

September Power BI Update Carousel

Power BI Monthly Update - September 2025

Check out the September 2025 Power BI update to learn about new features.

FabCon Atlanta 2026 carousel

FabCon Atlanta 2026

Join us at FabCon Atlanta, March 16-20, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM.