Power BI - Group Columns and Create new Columns

[anorville]

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

[rcharara · Accepted 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

[rcharara · Accepted 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

[anorville]

@rcharara 

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

Austin

[HotChilli · Accepted solution]

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. 

[anorville]

WOW! That was amazing... I need to learn more about Advanced Editor. LIFE CHANGER @HotChilli 

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

[anorville]

@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