Im trying to import data using Bex as source however prior importing would like to filter out the data which is not relevant and consider only what is meaningful...We do have default prompt to filter the data . However, i would like to write a M-code which will filter the data which required and omit the rest
When i write the below code the cost Element is importing all the data and filtering display name which has "ABC"
I want to only imprt data which has ABC and omit the rest . Can you please help me in generating the M-code for the same.
Solved! Go to Solution.
You need to update your Bex report so that it will filter this data out. There is nothing in Power BI you can do to prevent this data from loading. As you see, you can apply a filter to exclude the data but at that point the data has already been loaded to Power BI. The only thing you can do from M-Code to assist is populating a parameter to pass to the report to prevent the data from loading. You can’t create your own filters/parameters that you pass to the Bex query to filter your data, you can only pass parameters to the report that actually already exist for the Bex report. So as I said, if you write your bex report so that you’re bex report works correctly, then when you call it from Power BI it will work correctly.
Now there is no harm in having this data load into Power BI and then exclude the data they way you are. That data will not be saved in the report and will not cause your file size to increase but as you said it will have to load to power bi first before you can filter the data.
Are you talking about the parameters like controlling area and company code which is coming from Bex query??
I was thinking if Power query parameters can meet my requirement to filter the required data? do you have any other suggestion, its ok if the data is loading in power bi first and then later if we are omitting the irrelevant part
Correct, unless you're bex query has a paramter that allows you to filter the data then there is nothing you can do from Power Query. Power Query will only allow you to filter the results that have been loaded in this case.
When Power BI pulls the data from the BEx query its simply making a call to the existing query, passing in the required/optional paramters (That are a part of the BEx query) and then returning the results. So if you can't or don't have a way to filter the data out at the BEx side then you'll have to load the data to Power BI and then just filter the results through Power Query. I understand what you're after, ideally you only want to load data that your going to use within Power BI but due to the limiation that you have you'll have to load the data and then filter through Power query.
Thanks for your brief explanation helpmed me alot.
Can you suggest a way to filter data via power query( should i go with writing M-code) ? shall i go with Query Parameters any advice?
We're excited to announce our first cross-community 'Can You Solve These?' challenge!
Visit our Community Blog for articles, guides, and information created by fellow community members.
Wondering what events you could join or have an event to promote yourself? Check out our Upcoming Events.
We are thrilled to announce we will begin running a monthly webinar series named Power BI Dev Camp.