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
Anonymous
Not applicable

M-code for getting only required data with bex as source

Hi ,

 

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"

mc1.PNG

 

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.

@Anonymous 

Regards,

Husna

1 ACCEPTED SOLUTION
Anonymous
Not applicable

I wouldn't write m-code unless you have to. From power query, just click the column header and apply a text filter. 

2020-05-06_11-17-04.png

View solution in original post

7 REPLIES 7
Anonymous
Not applicable

@Anonymous 

 

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.

Anonymous
Not applicable

mc2.PNG

 

Are you talking about the parameters like controlling area and company code which is coming from Bex query?? 

And

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

@Anonymous 

Anonymous
Not applicable

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. 

Anonymous
Not applicable

Hi @Anonymous 

 

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?

 

Regards,

Husna

Anonymous
Not applicable

I wouldn't write m-code unless you have to. From power query, just click the column header and apply a text filter. 

2020-05-06_11-17-04.png

Anonymous
Not applicable

@Anonymous 

 

Thank you for your prompt response and very clear explanation....Atleast got to know which is the better option .

 

Regards,

Husna

Greg_Deckler
Super User
Super User

Not familiar with Bex, is there a connector for that? Not sure if it supports query folding, probably not. If that is the case, not sure about a solution. @ImkeF , @edhans 


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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.