Using Parameter to define if a table is loaded or not

[Timaru_Golf]

Hi,

I have a large data set that I have chunked up into separate Dataflows  of Current, Archive #1, Archive #2 and when developing and testing I only want to load the Current Dataflow

The code I have works -- BUT it reads in the the Dataflows before correctly not loading them in to the model.

Obviously this kind of defeats what I was trying to achieve i.e not read and load the Archive data.

At first I had the Dataflows referenced directly in the query below (but it still read then in ....) now I have moved to creating individual queries to just load each Dataflow and I have set these to Enable Load = OFF and you see me referancing these in the code below.

My code is as follows and referances 2 parameters

Load_Archive

Load_Archive2 

let
    Source = PowerBI.Dataflows(null),
   
    // Load Current
    
    #"Current Data"  = #"DAT_Report - Ingest",
    // Load Archive


    //Decide if we need to keep Archive Data  (Used to reduce Dataset size for Desktop)
    #"Load_Archive 1" = if Load_Archive = true 
 
               then  Table.Combine({#"Current Data", #"DAT_Report - Ingest [Archive #1]"})
               else  #"Current Data", 
   
    // Load Archive 2


    //Decide if we need to keep Archive Data  (Used to reduce Dataset size for Desktop)
    #"Load_Archive 2" = if Load_Archive2 = true 
   
               then  Table.Combine({#"Load_Archive 1", #"Archive 2"})
               else  #"Load_Archive 1", 

    #"Standardise Dates" = Table.TransformColumnTypes(#"Load_Archive 2",{{"Report Date", type date}, {"TargetWeek", type date}, {"ServiceWeek", type date}}),
    #"Filtered Rows" = Table.SelectRows(#"Standardise Dates", each [Report Date] >= ReportStartLoadDate),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"KEY_WSMT"})

in
        #"Removed Columns"

Any ideas what I am doing wrong?

[mahoneypat]

Your approach seems good, and I would have guessed it would work.  Since it doesn't, one other thing you can try is to use your True/False parameters to change the output of Load_Archive queries.  You could put an if in the last step of each.  If false, have it return null or a really small dummy dataset you then filter out.  The evaluation should skip loading the archive dataflows if the condition is not met (False).  So that way, if it won't avoid processing your archive queries, it will have much less work to do first (and not use the dataflow).

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[Timaru_Golf]

@mahoneypat  Good idea but really its the same solution as you said.

I got it to work though!!! 

I was referancing other queries that landed the DataFlows into the model first what I did is moved this landing queries into the code to be inside the condition check in the code I shared above and this now works as intended.

Cheers...

[lbendlin]

You're not doing anything wrong per se, that's more of a question if that is a bug in the lazy execution or not.  

Try to disable the parallel loading of sources - but it shouldn't really make a difference.

What you actually want to do is to control that at the source. For example if Current, Archive1, Archive2 etc are CSV files on a file share then you can easily control the Table.Combine by moving the archive files in and out of the source folder as needed.

[Timaru_Golf]

Hi, I tried disabling the parellel loading but no improvement.

The data sets are hundreds of csv files so I can't move the files for testing.

So it a bug in the evaluation process then?

Cheers

Andrew 

[Timaru_Golf]

Pinging this again.

Does anyone know how to use Parameter to define if a Dataflow is loaded into the model or not.

Seems simple - But I have tried it a number of ways and it still ingests all the data when you refresh the Desktop model instead of ignoring the ones that the logic would say not to look at.

Any ideas? 

[lbendlin]

"The data sets are hundreds of csv files so I can't move the files for testing."

Why not?

[Timaru_Golf]

Two reasons really,

1. The size they about a GB each and its never a good idea to mess with the source data
2. We have other models and users that mine this data so in effect taking it off line so we can test and dev models would be complicated and disruptive. 

[lbendlin]

1. file size is irrelevant if the move is within the same folder structure. All you do is change pointers.

2. Would it be an option for you to have your own copy of the source data?