I want to add multiple columns at the same time

[ilovegalois]

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.

[Adamboer]

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]

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,