Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
ilovegalois
Frequent Visitor

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.

2 REPLIES 2
Adamboer
Responsive Resident
Responsive Resident

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.

           

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,

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors