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 simplicity, let's say we have one fact table `Sales` and one dimension table `Products`.

Suppose the table `Sales` contains daily sales data. So we have the following columns: Date, Amount, Product Code

On the other hand, the table `Products` has two columns: Product Name, Product Code.

 

When I add new columns for the first day of the month and the product name to this table `Sales` at the same time, I use the following M code:

 

Table. ExpandRecordColumn(
Table.AddColumn(Sales, "Added Column", each [
#"First day of the month" = #date(Date.Year([Date]),Date.Month([Date],1) ,
#"Product Name" = Products{[#"Product Code" = [#"Product Code"]}[#"Product Name"]
]),

"Added Columns", {"First day of the month", "Product Name"})

 

I don't think this is a good way. This code is very hard to read.
The biggest reason is based on the specification of the symbol "[ ]".
The symbol "[ ]" is used in this code with two meanings, a record constructor operator and a field selection operator, which are nested many times.

 

I'm sure there is a better way. if you'd teach me.
Thank you in advance.

Need Help
Tips and Tricks

2 Replies

  • 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,