Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Delete Hard Coded Columns after PowerQuery GroupBy

Hello Everyone, 

 

Down below I've shared my groupby statement. 

 

 

In PowerQuery M this is the code:

 

#"Groupby Casenummer for min_wo" = Table.Group(#"Type to date", {"CaseNumber"}, {{"Min Werkordernummer", each List.Min([wo_number]), type nullable text}, {"ALL", each _, type table [Id=text, CaseNumber=nullable number, AccountId=nullable text, AssetId=nullable text, Type=nullable text, RecordTypeId=nullable text, Status=nullable text, IsClosed=logical, ClosedDate=nullable datetime, OwnerId=text, CreatedDate=datetime, Company_name__c=nullable text, Serienummer__c=nullable text, wo_serial=nullable text, wo_cause=nullable text, wo_dateofvisit=nullable date, wo_downtime=nullable logical, wo_endtime=nullable datetime, wo_starttime=nullable datetime, wo_number=nullable text, wo_enddate=nullable date, wo_startdate=nullable date]}})

I want to make the last line of the code dynamic, because if I add a column in an earlier step, I need to manually do the GroupBy everytime. 

 

Last line (copied from whole statement above):

 

{"ALL", each _, type table [Id=text, CaseNumber=nullable number, AccountId=nullable text, AssetId=nullable text, Type=nullable text, RecordTypeId=nullable text, Status=nullable text, IsClosed=logical, ClosedDate=nullable datetime, OwnerId=text, CreatedDate=datetime, Company_name__c=nullable text, Serienummer__c=nullable text, wo_serial=nullable text, wo_cause=nullable text, wo_dateofvisit=nullable date, wo_downtime=nullable logical, wo_endtime=nullable datetime, wo_starttime=nullable datetime, wo_number=nullable text, wo_enddate=nullable date, wo_startdate=nullable date]}})

 

Is there anyone who can help me with this one?

 

Thanks!

 

  • Anonymous's avatar
    Anonymous
    3 years ago

    HI Anonymous,

    Did you mean to make the grouped table structure dynamic changes instead of auto generated by M query functions?
    If that is the case, you can try to remove the table structure parts, then the result will be recognized as common table type without detailed definitions.

     

    #"Groupby Casenummer for min_wo" = Table.Group(#"Type to date", {"CaseNumber"}, {{"Min Werkordernummer", each List.Min([wo_number]), type nullable text}, {"ALL", each _, type table}})

     

    Notice: power query will cache the table structure into the query steps and not auto sync. So if your source structure changes, you need to manually check each step to fix the issue caused by these cache structures.

    Regards,

    Xiaoxin Sheng

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable

    HI Anonymous,

    Did you mean to make the grouped table structure dynamic changes instead of auto generated by M query functions?
    If that is the case, you can try to remove the table structure parts, then the result will be recognized as common table type without detailed definitions.

     

    #"Groupby Casenummer for min_wo" = Table.Group(#"Type to date", {"CaseNumber"}, {{"Min Werkordernummer", each List.Min([wo_number]), type nullable text}, {"ALL", each _, type table}})

     

    Notice: power query will cache the table structure into the query steps and not auto sync. So if your source structure changes, you need to manually check each step to fix the issue caused by these cache structures.

    Regards,

    Xiaoxin Sheng