Forum Discussion
Check if value exists across DirectQuery - Import
I have a composite model with a DirectQuery connection to a larger finance dataset and an import connection to a smaller table that has additional PO information. I was having problems with lines in the finance data being filtered out if there was not a PO associated with them, but I built a column that converts the blanks to null for the key column and added a null line in the smaller table so that is fixed. I am still having problems with some POs that are not in the smaller table, but I still need to show up in the table visual. I need to build a column or a measure that checks if a PO from the large dataset is in the smaller table. I am currently adding every possible number that could be a PO, which is resulting in a billion row table, and that is not ideal.
Hi,lbendlin
Thanks for your concern about this issue.
TREATAS can be used in other situations, but DirectQuery mode really doesn't support TREATAS.
And I would like to share some additional solutions below.
Hi,crobson29
I am glad to help you.
According to your description, you want to check if value exists across DirectQuery – Import?
If I understand you correctly, then you can refer to my solution.
Since TREATAS is not supported in DirectQuery mode, consider using a LEFT OUTER JOIN in the query that loads the data into the model.
Here's a little example that I hope you find helpful.
In the Power Query Editor click Merge Queries:
Select the corresponding table and corresponding field PO, and then select Left Outer in the Join kind.
Click on the red circle in the picture to expand the table:
Mismatched PO values are displayed as null values:
Here's some official documentation, hope it helps:
Tutorial: Shape and combine data in Power BI Desktop - Power BI | Microsoft Learn
Merge queries overview - Power Query | Microsoft Learn
I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Fen Ling,
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 Replies
Hi,lbendlin
Thanks for your concern about this issue.
TREATAS can be used in other situations, but DirectQuery mode really doesn't support TREATAS.
And I would like to share some additional solutions below.
Hi,crobson29
I am glad to help you.
According to your description, you want to check if value exists across DirectQuery – Import?
If I understand you correctly, then you can refer to my solution.
Since TREATAS is not supported in DirectQuery mode, consider using a LEFT OUTER JOIN in the query that loads the data into the model.
Here's a little example that I hope you find helpful.
In the Power Query Editor click Merge Queries:
Select the corresponding table and corresponding field PO, and then select Left Outer in the Join kind.
Click on the red circle in the picture to expand the table:
Mismatched PO values are displayed as null values:
Here's some official documentation, hope it helps:
Tutorial: Shape and combine data in Power BI Desktop - Power BI | Microsoft Learn
Merge queries overview - Power Query | Microsoft Learn
I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Fen Ling,
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.This approach will result in RI violations, and in generally bad query performance. Recommend you use TREATAS instead.
TREATAS is not supported in DirectQuery mode unfortunately.
