cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Resolver I
Resolver I

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.

@lmf232s 

Regards,

Husna

1 ACCEPTED SOLUTION

Accepted Solutions
Resolver II
Resolver II

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

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
Highlighted
Super User IV
Super User IV

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

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 


---------------------------------------

Putting square pegs in round holes since 1972.

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Highlighted
Resolver II
Resolver II

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

@HusnaBanu413 

 

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.

Highlighted
Resolver I
Resolver I

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

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

@lmf232s 

Highlighted
Resolver II
Resolver II

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

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. 

Highlighted
Resolver I
Resolver I

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

Hi @lmf232s 

 

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

Resolver II
Resolver II

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

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

Highlighted
Resolver I
Resolver I

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

@lmf232s 

 

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

 

Regards,

Husna

Helpful resources

Announcements
August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

July 2020 Community Highlights

July 2020 Community Highlights

Learn about the exciting things that happened in July.

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

We are thrilled to announce we will begin running a monthly webinar series named Power BI Dev Camp.

Power BI Desktop August 2020 Update

Power BI Desktop August 2020 Update

We have great updates this month! Click the link for the video with more info.

Top Solution Authors
Top Kudoed Authors