Check itemcode from SQL query against a list of codes from an excel import

[Neilos]

Hi All,

I have a query loaded directly from an SQL database into PowerBI and I also have a simple one column list of codes loaded from an excel file that will be updated regularly.
I need to check the itemcodes from the query against the list from excel to see if it is contained in the list.
If it is, then handle the data like this, if it isn't then handle the data like that.

If this was to be done in excel, I'd simply use an xlookup to return the itemcode if exists and "" (blank) if not, but I don't know where to start to get a similar functionality.

It would be great if any material could be pointed out so I can learn this.

Thank you in advance.

[Neilos]

@Tahreem24 Thanks for the continued attention.

Just realised it's not possible to attach files!

I've learnt a few things from putting this together, different way of approaching the issue.

Heres some screenshots of dummy data. I've copied the SQL into excel, reduced the dataset and removed names and codes.

'SQL' data

Item List

Relation between the two (Column D in 'SQL' data)

Output and expected output (My Utopia)

Two left tables are the current output. Bottom table is WITH the filter applied to items that appear in the list.
Right is how I would like to show the data.
A yearly total and a grand total.
A monthly total for all items and a percentage of the total for items on the list and not on the list.

From what I've figured out, unless I've gone down the wrong path, I think I need the ability to add another two colums and apply a separate filter for each? I this the best approach?

Thanks.

[v-achippa]

Hi @Neilos,

Thank you for reaching out to Microsoft Fabric Community.

Thank you @Tahreem24 for the prompt response.

Thank you for sharing the detailed explanation. Based on your requirement, the best approach is to keep the Excel list as a separate table in your model rather than merging it into the Sql table.

Create a relationship between the ItemCode columns in both tables like List[ItemCode] --> SQL[ItemCode] and then use DAX measures to calculate the in list and not in list totals dynamically.

This will give you exactly the Utopia layout you described, with correct totals and automatic updates when your excel list changes.

Thanks and regards,

Anjan Kumar Chippa

[v-achippa]

Hi @Neilos,

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution I have provided for the issue worked? or let us know if you need any further assistance.

Thanks and regards,

Anjan Kumar Chippa

[Neilos]

@Tahreem24 Thanks for the continued attention.

Just realised it's not possible to attach files!

I've learnt a few things from putting this together, different way of approaching the issue.

Heres some screenshots of dummy data. I've copied the SQL into excel, reduced the dataset and removed names and codes.

'SQL' data

Item List

Relation between the two (Column D in 'SQL' data)

Output and expected output (My Utopia)

Two left tables are the current output. Bottom table is WITH the filter applied to items that appear in the list.
Right is how I would like to show the data.
A yearly total and a grand total.
A monthly total for all items and a percentage of the total for items on the list and not on the list.

From what I've figured out, unless I've gone down the wrong path, I think I need the ability to add another two colums and apply a separate filter for each? I this the best approach?

Thanks.

[Tahreem24]

@Neilos It would be great if you can explain with sample example.

[Neilos]

@Tahreem24 Thanks for the quick response.
I would have included an screenshot of the data as that would explain better, but it has sensetive info included so chose not to.

I'll explain the data as I have it.

SQL consists of;

Payment Number
Date
Invoice Number
ItemCode
Description
Quantity
Price
Total

There are 6000+ lines.

Excel is a very simple single column of ItemCodes.

I need to check the item codes in SQL data to see if they are listed in the excel data.
Then I need to create tables of what is in the list and what isn't.

Hope that explains what I've asked better.
If a sample is needed I can put a file together with dummy data.

Thank you.

[Tahreem24]

@Neilos  Thanks for the explaination. Yeah share the sample (dummy data) with input and expected output.