Forum Discussion

o59393's avatar
o59393
Icon for Post Prodigy rankPost Prodigy
3 years ago
Solved

Group by gives error - Power Query

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.

  • 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

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

     

     

3 Replies

  • 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

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