Forum Discussion
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)
4 Replies
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:
Next, you can go to the column where you want to remove the nulls and 0 and do that.
sorry, to clarify, i'm looking for line 7 to line 36 to be actioned before the source is loaded to model.
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?
