Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Join us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.

Reply
RyanVSS
Frequent Visitor

Choose columns and filter data before modelling (Advanced source data cleanse)

i am working with a large dataset and having to complete complex operations, which means that the refresh time for this model is crazy long. I've identified that the processing time could be cut down dramatically if i can somehow choose the columns i need and apply some filtered before the data is ingested by powerbi. i know this is possible with SQL data sources, but my source is dataverse dynamics tables. 

 

is there a way to choose specific columns from source data and filter those columns, before i cleanse and model the data in powerbi? 

 

example: 

 

  • i want to choose 6 columns only from the CDS database table, before modelling the data. 
  • once the the columns to retrieve have been chosen, i want to filter the "question" column for 7 attributes
  • finally, i want to remove null and "0" values from the "value" column. 

 

i tried the below, but it's far from close to working 

 

 

 

 

let
    Source = CommonDataService.Database("-----------"),
    response = Table.SelectColumns(Source{[Schema="dbo",Item="response"]}[data],{"createdon", "owningbusinessunitname", "question", "value", "supportsessionname", "contact"})

///////filter column "question" for several "equal" to x (7 different values)
//////filter column "value" to remove "null" and "0" values 

in
    dbo_response

 

 

 

 

 

effectively, i am trying to execute line 7 to line 36 (below) before i start the complex data modelling. the reason i am trying to do that is because the original script has multiple pivot/unpivots and conditional indexing, which is causing the model to fail validation/load when working with the whole source data table. The script itself is fine when working with small sample set of data (10,000 rows) 

RyanVSS_0-1721746206426.png

 

 

4 REPLIES 4
audreygerred
Super User
Super User

Hi! When you are in Power Query, once you have the columns that you need, go to the column you want to filter on and select the 7 different items you want to filter to. That will add the necessary M-code in for you when you add teh applied step. It will look something like this: 

audreygerred_0-1721743366678.png

Next, you can go to the column where you want to remove the nulls and 0 and do that.





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

Proud to be a Super User!





sorry, to clarify, i'm looking for line 7 to line 36 to be actioned before the source is loaded to model. 

 

RyanVSS_0-1721745732491.png

 

I am assuming you are referring to query folding. Can you click on the steps in your applied steps and see if View Native Query is NOT greyed out in any of the steps, then becomes greyed out? If you see some that are NOT greyed out and then some that are, what is the first step you have where it is greyed out?





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

Proud to be a Super User!





source + navigation are greyed out in Powerbi Desktop... actually, it's greyed out for all steps. 

 

However, i can view the native query on the source table when viewing in DataFlow for step 2  - dbo_response = Source{[Schema="dbo",Item="response"]}[Data]) - and beyond 

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.