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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors
Top Kudoed Authors