Hello Fellow Power BI Users -
I hope you can help me with this. I need to use in the inserted column I created in my “CUSTOMER MASTER” table a value from the “CUSTOMER” table based on the following criteria:
If (CUSTOMER MASTER [Customer ID]) is found in (CUSTOMER [Customer ID], the value needs to be replaced by CUSTOMER [ Customer Group], otherwise, the value will remain the same (CUSTOMER MASTER [ Customer ID]
Thank you so much in advance 
Lucia
Ok sure,
First you should actually fix this in the query editor.
You should merge the two tables together by the Customer ID. Then you can get the two columns into the same table.
Then the formula would be crazy easy. Just a simple IF statement.
Do you want to give that a go.
I wouldn’t recommend doing this in the model because you just don’t need to and you’re create additional complexity that doesn’t need to be there.
Chrs
Sam
Hi Sam,
I already merged the queries, but since there are values in my (CUSTOMER MASTER [Customer ID] table that were not in my (CUSTOMER [Customer ID] table, I got a bunch of “null” values and the formula is not working.
The logic that I need to achieve now is:
If [Group ID] = null then [Customer ID] else [Group ID]
… Can you please guide me on this?
Thank you as always for your kind support.
Lucia
So the idea in the query editor would be to change the nulls into something else so that they can work in you formula.
Can you add the formula you currently are using?
Within the query editor you can use the replace values transformation and change the nulls into anything.
See here
Maybe even use replace errors.
I’m not sure exactly, but definitely you should be solving this here somehow.
The formula in you calculated column should be very simple.
Now that I’m looking at it, it seems you are trying to write a formula inside the query editor. Don’t do this.
Clean up the table in the query editor then create a calculated column instead. And create and IF statement here.
o.k Sam,
I already have replaced the null values with “blank” in the query editor and ready to do the calculations but now, I’m facing the following:
I would say there are two “types” of customers individual customers and then customers that are in a parent/child (group) relationship. Individuals customers are the ones that have only one store and customers that are in a parent/child (group) relationships are those who have multiple stores. Based on this, every customer gets a unique Customer ID but those customers that have a parent/child relationship, they are also assigned to a customer group ID.
The [Master Customer ID] Column is listing ALL customers (the individual customers AND the customers that are in a parent/child (group) relationship). The difficulty I’m having is with this last category of customers because they need to be identified from [Master Customer ID] in the [Customer_ID] column and be counted based on the value assigned in the [Customer Group] column just once, considering the parent/child (Customer Group ID) as a single unit and not each child individually. After this, the calculated column should include the individual customers + this last group (based on the single customer ID group).
We need to keep in mind that more than one value from the [Customer_ID] column will be assigned with the indistinct value from the column [Customer Group].
Could you please guide me on this?
Thank you again for your help
Lucia
Is it possible to see the model here? As this is getting a bit difficult to understand without seeing everything at play here.
It’s a bit confusing as I feel this should be quite easy to solve.
You could send through a example as if that works.
I just want to see exactly what you are seeing. This will be the quickest way to solve this from here.
Thanks
Sam,
Attached you can find the two tables. I merged the Customer table into the Customer Master table in the query editor replacing the nulls for blanks.
Customer Table.xlsx (226.7 KB)Thanks,
Lucia
Ok thanks.
I created a new model and brought in the two tables.
So we have the master table and then we need some customer details into this one.
So all I’m going to do is first merge them
Then will bring in all these columns to see what it looks like
This got me to here
I can clean this up a bit
Now I commit this and go to the model and create a calculated column in this table.
Make sure to disable the load of the previous table as well. You don’t need this is your model anymore.
Here’s the simple calculated column
Updated Customer ID =
IF( ISBLANK( 'Customer Master'[Customer Group] ), 'Customer Master'[Master Customer ID], 'Customer Master'[Customer Group] )
That’s it really.
Now you have your normalized column for the ID, and you don’t need that additional table anymore.
(I also realized by working through this getting rid of the ‘nulls’ was uneccassary, so you can skip that part)
See how you go working through this.
Attached
Employee ID merge example.pbix (218.2 KB)
Thank you so much Sam!!!
Just one more question … when you say “Make sure to disable the load of the previous table as well. You don’t need this is your model anymore” what do you mean? I’m not sure about the purpose of doing this …
Lucia
Great.
Sp the idea here is that you have now integrated this table into another one, so you don’t actually require it in your data model anymore.
This is all about cleaning up your model so you only have in there what you need.
The course to review on this as soon as you can is this one. This contains all my best practise tips around arranging everything in the query editor and in the data model.
Check out this video for more on disabling load on a query.
Trust me, this would be a good course module to review when you can. So much to learn within this one.
Chrs
Thank you Sam !
I will definately watch them!!! The content of your videos is amaizing and powerful. I must say that I have learned a lot since I joint this group!!!
Lucia
Great to hear. All the best.
