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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Elsie14
Advocate I
Advocate I

combine column into one column with if-statement

Dear Power BI users,

 

I have a problem which I can not find the answer to. I want to use the input of a table to create a new column in this table. My data looks something like this:

 

UPN                License1          License 2            License 3          License 4         License 5

SomeUPN       null                 1                         1                       null                 null

 

 

If a column of a license is filled with an "1", it means it has this kind of license. There are related costs to this type of license for example $5 to license 2 and $9 to license 3.

 

 I want my data to look something like this:

 

UPN                License1          License 2            License 3          License 4         License 5     TotalCosts

SomeUPN       null                 1                         1                       null                 null              14

 

I cannot seem to find the answer to this. Can you please help me?

 

With kind regards,

 

 

 

 

 

 

9 REPLIES 9
parry2k
Super User
Super User

Do you have rate stored in a different table? I believe rate is based on UPN. If that is the case then you need to create relation between your tables and then use simple formula to calculate cost, let me know if you need further assitance.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

I believe Cost is based on the sum of the individual license costs.  Each UPN could have different licenses, denoted by a null or numeric value under each license's column.

I solved this entirely within the query editor.  Assuming you have a License Costs table in this form:

 

License #  |  Cost

1               |  15

2               |  20

etc. 

 

you can do some ETL processes and then a merge to get costs.

 

On the table with the UPN column, select the UPN column and unpivot other columns.  Based on your description, on the new attribute column, replace "License " with nothing to get just the license # (which you will need to change to math the data type of the License # column in your cost table).  Now, depending on how you want to display the data, you have two options.  You can either remove the "null" rows or convert them to "0".  Then, you will do a merge using the license number from the UPN table and the Cost table.  This will get your license cost, and you can either remove the null rows or add a custom column that multiplies the Cost by the Value (0 or 1).

Let me add to my previous post:

 

You have the data as described and you want to add a cost column for each UPN, based on the sum of the costs of each of the licenses that are either present (1) or not present (0 or null).

 

Assuming the License Costs look up table, as I mentioned before, here are the steps you need to follow in the query editor:

 

1. Select the UPN column and "unpivot other columns"

2. Make sure the license # column matches the data type of the license # column in the License Cost table

3. Do a Merge to get the cost of each type of license

4. Add a custom column that multiplies the indicator column (1 or 0/null) by the Cost column (from the merge)

5. Group By using UPN for the group by column and the new column will sum the Cost column

6. Close and Apply, and then create a relationship (UPN is the key) between this table and the original data table

7. Insert a table visual, put UPN and each of the license columns from the original table, and the Cost column from the created table

 

Capture.PNG

You can see with my dummy data that it acheives what you are looking for

Hi @dkay84_PowerBI

 

It works! The only thing is I lose the other license columns, which I want to hold. My data looks like this:

 

UPN                  Costs

TestUPN            10,17

 

So it seems like the license columns are lost somewhere.

 

With kind regards,

I'm not sure why you are not getting the sam result as my example. Did you follow my process exactly?

 

One other thing to try is clicking on the dropdown arrow on each of the License fields that you dragged into your chart and selecting "Show data with no values"

Make sure that you drag each of the license columns from the original table (License 1, License 2, etc) in order, otherwise if you drag in the unpivoted License # column it wont work

As suggested by @dkay84_PowerBI, is the way to go, assuming you have rates tables, if not then you can always write dax as below and hard code prices (not elegant solution)

 

Cost = if(UPN[License1] = blank(), 0, UPN[License1]*2)+if(UPN[License2] = blank(), 0, UPN[License2]*5)+if(UPN[License3] = blank(), 0, UPN[License3]*9)+if(UPN[License4] = blank(), 0, UPN[License4]*15)+if(UPN[License5] = blank(), 0, UPN[License5]*20)


Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

vanessafvg
Super User
Super User

have you tried using the switch() funcatoin  or nested if statements could work too?  (although there might be  better options but those are what come to mind),  





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!




Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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