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

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
GuillaumeBD
Frequent Visitor

Power query grouping all

Hello, I'm grouping with the "All rows" option. The M Code however will select all field individually. If I add a column to my data source, I need to add it to the code. Is there an option to have the All option in the M Code instead of every single field? Thank you

 

GuillaumeBD_0-1699381544978.png

 

M Code

 

#"Lignes groupées1" = Table.Group(#"Valeur remplacée1", {"Adresse"}, {{"Nouveau", each Table.RowCount(_), Int64.Type}, {"Min Date", each List.Min([Date created]), type datetime}, {"T", each _, type table [Date created=datetime, Type=nullable text, Adresse=nullable text, Quartier=text, Lien=text]}}),


#"T développé1" = Table.ExpandTableColumn(#"Lignes groupées1", "T", {"Date created", "Type", "Adresse", "Quartier", "Lien"}, {"Date created", "Type", "Adresse.1", "Quartier", "Lien"}),

 

1 ACCEPTED SOLUTION

Hello @v-shex-msft , after the group step that you suggested, there is a simple solution for the expand all. You need to delete the original column (In this case "Address") before it reappears with the expand step. For the expand step, here's a good tutorial on how to do it.

 

https://goodly.co.in/expand-all-columns-dynamically-power-query/

 

Thank you

View solution in original post

3 REPLIES 3
v-shex-msft
Community Support
Community Support

Hi @GuillaumeBD,

In fact, power query will cache the table structure in the query steps and these structures not auto change.

For your requirement, you can try to remove the table structure definition part and modify the 'expand field' step to use 'Table.ColumnName' function to dynamic extract the table structure from previous step:

    #"Lignes groupées1" = Table.Group(
        #"Valeur remplacée1",
        {"Adresse"},
        {
            {"Nouveau", each Table.RowCount(_), Int64.Type},
            {"Min Date", each List.Min([Date created]), type datetime},
            {"T", each _, type table}
        }
    ),
    #"T développé1" = Table.ExpandTableColumn(
        #"Lignes groupées1", "T", Table.ColumnNames(#"Lignes groupées1"), Table.ColumnNames(#"Lignes groupées1")
    )

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Hello @v-shex-msft , your group step works great but not the table expand column. I get an error saying that "Adresse" already exists. It seems with this method that PQ is not able to make the difference between the column that is inside the table and the one already there. It thinks it's the same.

 

Thank you

Hello @v-shex-msft , after the group step that you suggested, there is a simple solution for the expand all. You need to delete the original column (In this case "Address") before it reappears with the expand step. For the expand step, here's a good tutorial on how to do it.

 

https://goodly.co.in/expand-all-columns-dynamically-power-query/

 

Thank you

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

March 2024 PBI Gallery Image

Power BI Monthly Update - March 2024

Check out the March 2024 Power BI update to learn about new features.