Forum Discussion

RyanVSS's avatar
RyanVSS
Frequent Visitor
2 years ago

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...
Need Help
Show and Tell
audreygerred's avatar
audreygerred
Icon for Super User rankSuper User
2 years ago

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.

  • RyanVSS's avatar
    RyanVSS
    Frequent Visitor
    2 years ago

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

     

     

    • audreygerred's avatar
      audreygerred
      Icon for Super User rankSuper User
      2 years ago

      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?

      • RyanVSS's avatar
        RyanVSS
        Frequent Visitor
        2 years ago

        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