Forum Discussion

Frequent Visitor

2 years ago

Use column value in new column formula

I have a source table (IVS_DATA) with a lot of columns, each holding boolean values (TRUE, FALSE or null). Column headers are 3-digit reason codes (e.g. '209'). Now, I want to create a new table ...

Frequent Visitor

2 years ago

Greg_Deckler That would make my source table explode. My source table easily contains many hundredthousands records, and there are about 20 reason colums for each record ...

Anonymous
Not applicable
2 years ago
Hi Streeph ,

According to your statement, I think the column amount like 209 in IVS_DATA table should be the same as the row amount of the data in Reason column.

As mentioned as above, the calculation in Power BI is based on columns. So it is better you to transform your data by UNPIVOT function to make IVS_DATA contain [REASON] column and [Status] column.

If there are too many data in your table and this function will explode your table into too many rows, you can do some optimazation on your table.

For reference: Optimization guide for Power BI - Power BI | Microsoft Learn

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
- Streeph
  Frequent Visitor
  2 years ago
  Hi Anonymous, I tried unpivoting, but (apart from a huge multiplication of rows) that also lead to duplicating the unique identifiers in my source table...
  
  Before unpivot
  Reason_1 Reason_2 Reason_3
  ID1 TRUE FALSE FALSE
  ID2 FALSE TRUE TRUE
  ID3 FALSE TRUE FALSE
  
  After unpivot:
  Reason Value
  ID1 Reason_1 TRUE
  ID1 Reason_1 FALSE
  ID1 Reason_1 FALSE
  ID2 Reason_2 FALSE
  ID2 Reason_2 TRUE
  ID2 Reason_2 TRUE
  ID3 REason_3 FALSE
  ID3 REason_3 TRUE
  ID3 REason_3 FALSE

Related content