Forum Discussion

alice11987's avatar
alice11987
Helper I
3 years ago
Solved

Update Table based on another table

Hi there,   I have two tables, Sales table & Backorder table.   I will need to include Backorder table into Sales table based on Date & Inv ID:  1. Check if Date & Inv ID is the same; add the Am...
Need Help
  • Ashish_Mathur's avatar
    Ashish_Mathur
    3 years ago

    Hi,

    Does this M code work?

    let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Inv ID", Int64.Type}, {"Sales type", type text}, {"Amount", Int64.Type}}),
    #"Appended Query" = Table.Combine({#"Changed Type", Table2}),
    #"Grouped Rows" = Table.Group(#"Appended Query", {"Date", "Inv ID"}, {{"Total", each List.Sum([Amount]), type nullable number}, {"Type", each List.Max([Sales type]), type nullable text}})
in
    #"Grouped Rows"

    Hope this helps.

Ashish_Mathur's avatar
Ashish_Mathur
Super User
3 years ago

Hi,

Append the two tables.  Select Date and Inv ID columns, group by them and sum the numbers in the amount column.

Hope this helps. 

alice11987's avatar
alice11987
Helper I
3 years ago

Hi Ashish,

 

Thank you for reply. Can I know if it is possible if I would like to keep other columns from Table A when I group by. 

 

For e.g., There is "Period" column in Table A and not in Table B, but end result, I would like to remain "Period" column as follow:

thank you

 

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    3 years ago

    Hi,

    Does this M code work?

    let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Inv ID", Int64.Type}, {"Sales type", type text}, {"Amount", Int64.Type}}),
    #"Appended Query" = Table.Combine({#"Changed Type", Table2}),
    #"Grouped Rows" = Table.Group(#"Appended Query", {"Date", "Inv ID"}, {{"Total", each List.Sum([Amount]), type nullable number}, {"Type", each List.Max([Sales type]), type nullable text}})
in
    #"Grouped Rows"

    Hope this helps.

    • alice11987's avatar
      alice11987
      Helper I
      3 years ago

      Hi Ashish,

       

      Thank you so much! One last question...

       

      What if I append the two tables as new query, how should I edit on the above M code to keep all other columns when I group by?

       

      thank you

      • Ashish_Mathur's avatar
        Ashish_Mathur
        Super User
        3 years ago

        You are welcome.  Everything should remain the same.  Just try it through the UI yourself.