Pass a filename to another query to be used as data source

[Anonymous]

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"

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?

[v-juanli-msft]

Hi @Anonymous 

Please check if AlB's answer helps you.

If not, please share more details:

there are two queries above,

does the first query import data from web into Power BI,

then second query select the lastest data and show the file name of the last data,

finally, you want to use the file name of the last data to filter in the first query and return data.

Best Regards

Maggie

[Anonymous]

Hi @v-juanli-msft , @AlB 

I followed AIB suggestion and it works in PBI desktop (can refresh) but in PBI service does not refresh and gives error Unable to combine data...Please rebuild this data combination. I tried to research abit and one article says Power Query engine is not allowed to access two different data sources. So I still need help (anybody) to combine/merge the two data source query. Thanks

[v-juanli-msft]

Hi @Anonymous 

If changing the privacy level helps you?

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

Best Regards

Maggie

[Anonymous]

It works locally in PBI desktop, but not on the Power BI Web Service. I think i am facing the first problem, so I am seeking solution for the third and I'm not familiar with custom connector 😞

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

1. 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.
2. 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
3. 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.

[AlB]

Hi @Anonymous 

If the final result of your Table1 query is what you show in the pic, you can just:

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

Please mark the question solved when done and consider giving kudos if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[Anonymous]

I am getting error below Unable to combine data...Please rebuild this data combination when i tried to refresh in PBI service. Can someone help to combine my two queries into one?

"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://....