none
Pass a filename to another query to be used as data source RRS feed

  • Question

  • Hi,

     

    In Table 1, I have filtered the latest file name and I want to use this file name as data source in Query 1

     

    let
    Source = Web.BrowserContents("https://companysales.com:timeReportHourly=username"),
    #"Extracted Table From Html" = Html.Table(Source, {{"Column1", "TABLE > * > TR > :nth-child(1)"}, {"Column2", "TABLE > * > TR > :nth-child(2)"}, {"Column3", "TABLE > * > TR > :nth-child(3)"}, {"Column4", "TABLE > * > TR > :nth-child(4)"}}, [RowSelector="TABLE > * > TR"]),
    #"Promoted Headers" = Table.PromoteHeaders(#"Extracted Table From Html", [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"", type text}, {"File Name", type text}, {"Size (bytes)", Int64.Type}, {"Date Modified", type text}}),
    #"Extracted Text Before Delimiter" = Table.TransformColumns(#"Changed Type", {{"Date Modified", each Text.BeforeDelimiter(_, ","), type text}}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Extracted Text Before Delimiter",{{"Date Modified", type datetime}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type1", let latest = List.Max(#"Changed Type1"[Date Modified]) in each [Date Modified] = latest)
    in
    #"Filtered Rows"

    question.jpg

    let
    Source = Excel.Workbook(Web.Contents("https://companysales.com:", [RelativePath="timeReportHourly_20200531.xlsx"]), null, true)
    in
    Source

     

    After filter which one is the latest file, I need to use this as input to another query. Does this need a function or parameter? How can this be achieve?

    Tuesday, June 2, 2020 4:42 AM

Answers

  • Hi marc_hll,

    the third option ("masking your Data Source inside Functions") would look like so:

    () =>
    
    let
    
    Source = Web.BrowserContents("https://companysales.com:timeReportHourly=username"),
    #"Extracted Table From Html" = Html.Table(Source, {{"Column1", "TABLE > * > TR > :nth-child(1)"}, {"Column2", "TABLE > * > TR > :nth-child(2)"}, {"Column3", "TABLE > * > TR > :nth-child(3)"}, {"Column4", "TABLE > * > TR > :nth-child(4)"}}, [RowSelector="TABLE > * > TR"]),
    #"Promoted Headers" = Table.PromoteHeaders(#"Extracted Table From Html", [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"", type text}, {"File Name", type text}, {"Size (bytes)", Int64.Type}, {"Date Modified", type text}}),
    #"Extracted Text Before Delimiter" = Table.TransformColumns(#"Changed Type", {{"Date Modified", each Text.BeforeDelimiter(_, ","), type text}}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Extracted Text Before Delimiter",{{"Date Modified", type datetime}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type1", let latest = List.Max(#"Changed Type1"[Date Modified]) in each [Date Modified] = latest)
    in
    #"Filtered Rows"

    That creates a function without arguments. 
    Next you have to adjust the query in which you've referenced this query like so:

    let
    Source = Excel.Workbook(Web.Contents("https://companysales.com:", [RelativePath=Table1()[File Name]), null, true)
    in 
    Source


    Imke Feldmann - MVP Data Platform (PowerBI) - http://www.TheBIccountant.com 

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful - Thanks!


    Sunday, June 14, 2020 6:45 AM
    Moderator

All replies

  • In two steps to help you understand:

    let
    reference = #"Table 1" [File Name] {0},    //{0} to get the text of the record
    Source = Excel.Workbook(Web.Contents("https://companysales.com:", [RelativePath=reference]), null, true)
    in Source





    • Edited by anthony34 Tuesday, June 2, 2020 6:05 AM
    Tuesday, June 2, 2020 5:40 AM
  • It works in PBI desktop, but does not refresh successfully in PBI service

    Below is the error when I refresh in PBI service. Is there any workaround? Is PBI not allowed to access two different data sources

    {"error":{"code":"DM_GWPipeline_Gateway_MashupDataAccessError","pbi.error":{"code":"DM_GWPipeline_Gateway_MashupDataAccessError","parameters":{},"details":[{"code":"DM_ErrorDetailNameCode_UnderlyingErrorCode","detail":{"type":1,"value":"-2147467259"}},{"code":"DM_ErrorDetailNameCode_UnderlyingErrorMessage","detail":{"type":1,"value":"[Unable to combine data] Section1/Hourly/AutoRemovedColumns1 references other queries or steps, so it may not directly access a data source. Please rebuild this data combination."}},{"code":"DM_ErrorDetailNameCode_UnderlyingHResult","detail":{"type":1,"value":"-2147467259"}},{"code":"Microsoft.Data.Mashup.MashupSecurityException.DataSources","detail":{"type":1,"value":"[{\"kind\":\"Web\",\"path\":\"https://

    Tuesday, June 2, 2020 3:08 PM
  • Hi

    Is PBI not allowed to access two different data sources
    I can't say for sure as I can't use it but I'm pretty sure it can. The issue you're facing is a Formula.Firewall error that you can found in what you posted as "
    AutoRemovedColumns1 references other queries or steps, so it may not directly access a data source. Please rebuild this data combination"

    Can't help further right now as your queries don't seem to be as in initial post. Could you re-post the same kind of pic. and your queries code?

    Thanks

    Tuesday, June 2, 2020 3:45 PM
  • Here you go @Lz._

    In Table 1, I have filtered the latest file name and I want to use this file name as data source in Query 1

    let

    Source = Web.BrowserContents("https://companysales.com:timeReportHourly=username"),
    #"Extracted Table From Html" = Html.Table(Source, {{"Column1", "TABLE > * > TR > :nth-child(1)"}, {"Column2", "TABLE > * > TR > :nth-child(2)"}, {"Column3", "TABLE > * > TR > :nth-child(3)"}, {"Column4", "TABLE > * > TR > :nth-child(4)"}}, [RowSelector="TABLE > * > TR"]),
    #"Promoted Headers" = Table.PromoteHeaders(#"Extracted Table From Html", [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"", type text}, {"File Name", type text}, {"Size (bytes)", Int64.Type}, {"Date Modified", type text}}),
    #"Extracted Text Before Delimiter" = Table.TransformColumns(#"Changed Type", {{"Date Modified", each Text.BeforeDelimiter(_, ","), type text}}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Extracted Text Before Delimiter",{{"Date Modified", type datetime}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type1", let latest = List.Max(#"Changed Type1"[Date Modified]) in each [Date Modified] = latest)
    in
    #"Filtered Rows"

    question.jpg

    let
    Source = Excel.Workbook(Web.Contents("https://companysales.com:", [RelativePath="timeReportHourly_20200531.xlsx"]), null, true)
    in
    Source

    After filter which one is the latest file, I need to use this as input to another query. Does this need a function or parameter? How can this be achieve?

    Tuesday, June 2, 2020 10:21 PM
  • Still same error in PBI web service, work locally in PBI desktop, but not on the Power BI Web Service.

    Data source error: {"error":{"code":"DM_GWPipeline_Gateway_MashupDataAccessError","pbi.error":{"code":"DM_GWPipeline_Gateway_MashupDataAccessError","parameters":{},"details":[{"code":"DM_ErrorDetailNameCode_UnderlyingErrorCode","detail":{"type":1,"value":"-2147467259"}},{"code":"DM_ErrorDetailNameCode_UnderlyingErrorMessage","detail":{"type":1,"value":"[Unable to combine data] Section1/Hourly/AutoRemovedColumns1 references other queries or steps, so it may not directly access a data source. Please rebuild this data combination."}},{"code":"DM_ErrorDetailNameCode_UnderlyingHResult","detail":{"type":1,"value":"-2147467259"}},{"code":"Microsoft.Data.Mashup.MashupSecurityException.DataSources","detail":{"type":1,"value":"[{\"kind\":\"Web\",\"path\":\"https://........\"}]"}},{"code":"Microsoft.Data.Mashup.MashupSecurityException.Reason","detail":{"type":1,"value":"PrivacyError"}}],"exceptionCulprit":1}}} Table: Hourly

    I read an article https://www.poweredsolutions.co/2019/03/12/data-privacy-and-the-formula-firewall/

    What options do I have to fix this? You have 3 options:

    Ignore Privacy Levels – You’ve probably read this one before, but you can just tick the box to ignore the privacy levels. This will work locally, but not on the Power BI Web Service.
    Create a Power BI Custom Connector – this is BY FAR the best option as you can make sure that your queries can run in an optimal way and work seamlessly in the web service as well. Not to mention that you also have some features that are unique to custom connectors like being able to read the response headers of your calls and using setting your OAuth 2.0 flow. Sadly, this is only available for Power BI, but if you’re only going to work within Excel then the first option should be sufficient for your case
    Embedding or masking your Data Sources inside Functions – this method will make your data sources not visible to the Data Privacy Levels at first, but you can tune your queries to define the data source at first and then apply a function

    I think i am facing the first problem, so I am seeking solution for the third and I'm not familiar with custom connector :(

    Thursday, June 4, 2020 3:32 PM
  • Hi Marc

    I can't help with this. As I earlier said I don't have access to Power BI Web Service

    Suggestion: change the title of this thread with something like: Power BI Web Service Firewall error

    and look for similar issues on the Power BI dedicated forum

    Thursday, June 4, 2020 4:54 PM
  • Hi Marc. Generally speaking, dynamic data sources are unfortunately not supported in the PBI service. Feel free to request this via the PBI Uservoice.

    However, if the only dynamic part of the data source is the RelativePath option of Web.Contents, then this should work in the service. (RelativePath was created to allow dynamism in Web.Contents to work in the PBI service.)

    Ehren

    Friday, June 5, 2020 9:23 PM
    Owner
  • Hi marc_hll,

    the third option ("masking your Data Source inside Functions") would look like so:

    () =>
    
    let
    
    Source = Web.BrowserContents("https://companysales.com:timeReportHourly=username"),
    #"Extracted Table From Html" = Html.Table(Source, {{"Column1", "TABLE > * > TR > :nth-child(1)"}, {"Column2", "TABLE > * > TR > :nth-child(2)"}, {"Column3", "TABLE > * > TR > :nth-child(3)"}, {"Column4", "TABLE > * > TR > :nth-child(4)"}}, [RowSelector="TABLE > * > TR"]),
    #"Promoted Headers" = Table.PromoteHeaders(#"Extracted Table From Html", [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"", type text}, {"File Name", type text}, {"Size (bytes)", Int64.Type}, {"Date Modified", type text}}),
    #"Extracted Text Before Delimiter" = Table.TransformColumns(#"Changed Type", {{"Date Modified", each Text.BeforeDelimiter(_, ","), type text}}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Extracted Text Before Delimiter",{{"Date Modified", type datetime}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type1", let latest = List.Max(#"Changed Type1"[Date Modified]) in each [Date Modified] = latest)
    in
    #"Filtered Rows"

    That creates a function without arguments. 
    Next you have to adjust the query in which you've referenced this query like so:

    let
    Source = Excel.Workbook(Web.Contents("https://companysales.com:", [RelativePath=Table1()[File Name]), null, true)
    in 
    Source


    Imke Feldmann - MVP Data Platform (PowerBI) - http://www.TheBIccountant.com 

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful - Thanks!


    Sunday, June 14, 2020 6:45 AM
    Moderator