cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper III
Helper III

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

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?

6 REPLIES 6
Community Support
Community Support

Hi @marc_hll 

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

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

Hi @marc_hll 

If changing the privacy level helps you?

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

 

 

Best Regards

Maggie

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.
Super User III
Super User III

Hi @marc_hll 

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 

SU18_powerbi_badge

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

 

Helpful resources

Announcements
secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

Wave Release 2

Check out the updates in Power BI.

Overview of Power BI 2020 release wave 2!

Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors