Group by gives error - Power Query

[o59393]

Hi all

I am doing a group by ID in Power Query with the following code:

= Table.Group(
#"Changed Type", {"Id"}, 
{
{"Title", each Text.Combine(List.Distinct([Title]),", ")}, 
{"Status", each Text.Combine(List.Distinct([Status]),", ")}, 
{"Category", each Text.Combine(List.Distinct([Category]),", ")},
{"PO Placed?", each Text.Combine(List.Distinct([#"POplaced?"]),", ")},
{"Project Type", each Text.Combine(List.Distinct([Project Type]),", ")},
{"Zone", each Text.Combine(List.Distinct([Zone]),", ")}, 
{"Region", each Text.Combine(List.Distinct([Region]),", ")},
{"Country", each Text.Combine(List.Distinct([Country]),", ")}, 
{"Bottler", each Text.Combine(List.Distinct([Bottler]),", ")},
{"Package", each Text.Combine(List.Distinct([Package]),", ")},
{"OEM", each Text.Combine(List.Distinct([OEM]),", ")},
{"LineSpeed", each Text.Combine(List.Distinct([LineSpeed]),", ")},
{"Description", each Text.Combine(List.Distinct([Description]),", ")},
{"Annual Capacity (Unit Cases)", each List.Max([#"Annual Capacity (Unit Cases)"]), type nullable number}, 
{"CAPEX (USD)", each List.Max([#"CAPEX (USD)"]), type nullable number}, 
{"Purchase Order Date", each List.Max([Purchase Order Date]), type nullable date}, 
{"Estimated Start-Up Date", each List.Max([#"Estimated Start-Up Date"]), type nullable date}}
)

Everything works well except for the region column, which was added with a merged columns feature as seen below:

Here is the error it shows:

Expression.Error: The column 'Region' of the table wasn't found.
Details:
Region

What could be wrong?

Please click on the photos to zoom in.

Thanks.

[OwenAuger · Accepted solution]

@o59393 

Your Grouped Rows1 step appears to be referring to the Changed Type step, which is not the immediately preceding step, which is presumably not as intended.

Try changing the code of the step to:

= Table.Group(
#"Expanded Region"
...

Regards

[o59393]

Thank you both! @danextian  @OwenAuger 

[danextian · Accepted solution]

Hi @o59393,

Your Group By is referencing #"Changed Type" applied step. The merging and expanding came after that so [Region] still didn't exist before that thus the error. Replace it with #"Expanded Region"

[OwenAuger · Accepted solution]

@o59393 

Your Grouped Rows1 step appears to be referring to the Changed Type step, which is not the immediately preceding step, which is presumably not as intended.

Try changing the code of the step to:

= Table.Group(
#"Expanded Region"
...

Regards