Power query grouping all

[GuillaumeBD]

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

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

[Anonymous]

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

[GuillaumeBD]

Hello @Anonymous , 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

[GuillaumeBD · Accepted solution]

Hello @Anonymous , 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