Forum Discussion

ilovegalois's avatar
ilovegalois
Frequent Visitor
3 years ago

I want to add multiple columns at the same time

hello and thanks for reading   I'm trying to add multiple new columns to a table in Power Query at the same time. Additional columns can be a bit complex, such as referencing other tables.   For...
Need Help
Tips and Tricks
Adamboer's avatar
Adamboer
Icon for Responsive Resident rankResponsive Resident
3 years ago

It is possible to simplify the M code to make it more readable and easier to understand. One approach is to break down the steps into smaller, more manageable ones. Here's an example:

  1. Create a new column that extracts the year and month from the Date column:

    SalesWithMonth = Table.AddColumn(Sales, "Month", each Date.StartOfMonth([Date]))
  2. Merge the Products table with the SalesWithMonth table, matching on the Product Code column:
    SalesWithProduct = Table.NestedJoin(SalesWithMonth,{"Product Code"},Products,{"Product Code"},"Product",JoinKind.LeftOuter)


    1. Extract the Product Name from the merged table:

      SalesWithProductName = Table.AddColumn(SalesWithProduct, "Product Name", each [Product][Product Name])

       

      1. Filter the table to only include the first day of the month:

        SalesFirstDayOfMonth = Table.SelectRows(SalesWithProductName, each Date.Day([Date]) = 1)

         

        1. Select the final columns for the output table:

           

          OutputTable = Table.SelectColumns(SalesFirstDayOfMonth,{"Date", "Amount", "Product Code", "Product Name"})

          This approach breaks down the process into smaller, more manageable steps, making it easier to understand and modify.

           

  • ilovegalois's avatar
    ilovegalois
    Frequent Visitor
    3 years ago

    thank you for teaching me.

     

    For simplicity, I've added only two columns, the first day of the month and the product name column, but I might probably add 10 or 20 columns.

     

    Using the method you just taught me, I could add multiple columns from the Products table at the same time, only if I "left outer join" the columns.

    However, if I add a calculated column that uses an existing column, such as the first day of the month, the number of steps will increase by the number of columns I add, so I would avoid it if possible.

     

    I'd like to have as few steps as possible just adding columns.

     

    Best Regards,