cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Frequent Visitor

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

@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. 

View solution in original post

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.

 

@anorville 

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

@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
secondImage

Happy New Year from Power BI

This is a must watch for a message from Power BI!

December Update

Check it Out!

Click here to read more about the December 2020 Updates!

Community Blog

Check it Out!

Click here to read the latest blog and learn more about contributing to the Power BI blog!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.