none
How to dynamically control the source using a function RRS feed

  • Question

  • Hi,

    I want to read the source data using the below:

    I have a file with Input Data as 

    Date       State SecondState

    201409  GA     CA

    I have written my first function to read this file:

    let
        InputSource = Csv.Document(File.Contents("Z:\InputData.txt"),null,",",null,1252),
        #"First Row as Header" = Table.PromoteHeaders(InputSource),
        #"Changed Type" = Table.TransformColumnTypes(#"First Row as Header",{{"Month", type text}, {"State", type text}, {"SecondState", type text}})
    in
        #"Changed Type"

    I have written the second function to read the above function by providing an input:

                 

    let
        GetSourceData1 = (InputRead as text, InputStateRead as text, InputfileType as text) =>
    let
        InputState = if InputRead = "1" and InputStateRead = "1" then List.First(GetInputData1[State])
    else if InputRead = "2" and InputStateRead = "1" then List.Last(GetInputData1[State])
    else if InputRead = "1" and InputStateRead = "2" then List.First(GetInputData1[SecondState])
    else List.Last(GetInputData1[SecondState]),
        InputMonth = if InputRead = "1" then List.First(GetInputData1[Month]) else List.Last(GetInputData1[Month]),
        InputFolder = Text.Combine({"\\testlocation\", InputMonth}, "\"), 
        InputFileTypeFull = Text.Combine({InputState, InputfileType}, "_"),    
        PreFilename = Text.Combine({InputFolder, InputFileTypeFull}, "\"),
        Filename = Text.Combine({PreFilename, "txt"}, ".")
    in
        Filename
    in
        GetSourceData1

    And I'm creating a query to read the source to load the data with the below:

    let
        Source = Csv.Document(File.Contents(GetSourceData1("1","1","company")),null,",",null,1252),

    And I'm receiving the following error

    Formula.Firewall: Query 'Query1' (step 'Source') references other queries or steps and so may not directly access a data source. Please rebuild this data combination.

    Is there any way I can control the file location with name dynamically? I have my data in two different files: \\testlocation\201409\GA_company.csv & \\testlocation\201409\CA_company.csv 

    I want to control dynamically so that I can tweak dates & file names using the same function.

    Thanks for looking into this.



    Tuesday, November 25, 2014 6:45 AM

Answers

All replies

  • Can you try changing the beginning of the query to read the source as follows?

    let
        CallMyFunction = GetSourceData1("1","1","company"),
        Source = Csv.Document(File.Contents(CallMyFunction),null,",",null,1252),
    

    Power Query's data privacy rules prevent it from accessing two different data sources in the same step. Breaking the expression up into two steps, as I've done here (one to call the function, one to use the output of the function to get the CSV file) should get around this. You may still be prompted to set data privacy settings for these data sources but it should now work.

    Chris


    Check out my MS BI blog I also do SSAS, PowerPivot, MDX and DAX consultancy and run public SQL Server and BI training courses in the UK

    Tuesday, November 25, 2014 9:24 AM
  • Hi,

    I tried this and I'm still getting the same error. It didn't prompt the data privacy settings in this case for me. Please let me know if I have any other alternative.

    Thanks

    Tuesday, November 25, 2014 3:54 PM
  • Are you sure it's the same error message? In any case, the next thing to try is clicking the Fast Combine button:

    https://support.office.com/en-au/article/Privacy-levels-cc3ede4d-359e-4b28-bc72-9bee7900b540#__toc381720831

    Does this make the query work now?

    Chris


    Check out my MS BI blog I also do SSAS, PowerPivot, MDX and DAX consultancy and run public SQL Server and BI training courses in the UK

    Tuesday, November 25, 2014 3:59 PM
  • Yes, it gave me the same error message. Unfortunately the data I'm working does have sensitive Information and I dont think I can leverage the Fast Combine option as this option clearly states not to use this if the Information is sensitive. But I'm really surprised that we can't actually drive the source location dynamically as you don't want to provide a hard coded location every time to read the source. Not sure what I'm missing here. Thanks.

    Tuesday, November 25, 2014 4:04 PM
  • You only need to worry about setting the Fast Combine option if it would involve sending sensitive data to an external data source. In this case it doesn't look as though Power Query is sending data anywhere outside the machine the query is running on, and even then the data that's being passed between queries is just file names, so I think you're safe.

    Otherwise, can you paste the code you're currently using the for query that gives the error?

    Chris


    Check out my MS BI blog I also do SSAS, PowerPivot, MDX and DAX consultancy and run public SQL Server and BI training courses in the UK

    Tuesday, November 25, 2014 4:12 PM
  • Chris,

    Since I'm not signing in, I took your advice and enable FAST COMBINE and its working now. Thanks for all the help.

    Tuesday, November 25, 2014 4:52 PM