CODEWITHSUNDEEP
powerbidaxpowerquery
Pradeep Kumar
Pradeep Kumar

Reputation: 17

How to create multiple columns at once which depends on each other in Power BI?

I am new in Power BI, have a table in excel. I need to create the same in Power BI.

I have one table in Excel which has the following fields:

After first sorting the table based on ID and Date, and then adding Column A, Column B and Column C, where Column A value is equal to previous value of Column C and Column C value is equal to sum of Column A and Column B. The desired table would be:

where,

Column A = IF($A2=$A1,$G1,0)
Column B = D2-E2
Column C = SUM(E2:F2)

How to achieve this in Power BI?

Upvotes: 1

Views: 4740

Answers (1)

Marc Pincince
Marc Pincince

Reputation: 5192

I believe this may be something like what you are looking for. It requires an index so that you can use the index to reference the previous row's values.

Instead of calculating Column A from Column C like your formula suggested ($G1), I calculated it from [Revenue]. That's because Column C didn't exist yet, and wouldn't exist yet, because it wouldn't be calculated until Column A (E in your formula) exists.

Anyhow, if your table is named Table1 and has [ID], [Name], [Date], and [Revenue] just like in your first image, then you should be able to use this code. Otherwise, change "Table1" to whatever your table name is. Also, you'll need to replace "YourPathAndFile.xls" below with your path and file name--something like "C:\Users\yourname\somefolder\filename.xls". Include the quotes for both the table name and the path and file, just like below.

let
  Source = Excel.Workbook(File.Contents("YourPathAndFile.xls"), null, true),
  Table1_Table = Source{[Item="Table1",Kind="Table"]}[Data],
  #"Changed Type" = Table.TransformColumnTypes(Table1_Table,{{"ID", type text}, {"Name", type text}, {"Date", type date}, {"Revenue", Currency.Type}}),
  #"Sorted Rows" = Table.Sort(#"Changed Type",{{"ID", Order.Ascending}, {"Date", Order.Ascending}}),
  #"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Index", 0, 1, Int64.Type),
  #"Added Custom" = Table.AddColumn(#"Added Index", "Column A", each if [Index] = 0 then 0 else if [ID] = #"Added Index"{[Index]-1}[ID] then #"Added Index"{[Index]-1}[Revenue] else 0),
  #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom B", each [Revenue] - [Column A]),
  #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Custom C", each [Column A]+[Custom B]),
  #"Removed Columns" = Table.RemoveColumns(#"Added Custom2",{"Index"})
in
  #"Removed Columns"

What I did was:

  1. Started Power BI
  2. Got the data from the spreadsheet and added the table (Table1) to Power Query (Get Data > All > Excel > Connect > Navigated to the excel file > Double-clicked the file (Could have selected the file and clicked Open instead) > Selected the relevant table (Table1) > Transform Data)
  3. I made sure the columns' data types were what I wanted. (Select a column > Transform > Data Type: > select appropriate type > repeat for each column necessary)
  4. I sorted the ID and Date columns (Select [ID] > click drop-down arrow > click Sort Ascending > repeat for [Date])
  5. I added an index column (Add Column > Index Column)
  6. I added Column A (Add Column > Custom Column > fill in as below and click OK)

enter image description here

  1. I added Column B (Add Column > Custom Column > fill in as below and click OK)

enter image description here

  1. I added Column C (Add Column > Custom Column > fill in as below and click OK)

enter image description here

  1. I removed the Index column (Right-click [Index] > Remove)

The result is:

enter image description here

Upvotes: 1

Related Questions