Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
soundwave123
Frequent Visitor

Using web.content xml to stage data then reference it for multiple queries

 

1. Original query to get the unique code (D20171031T115921-CBTFFOOKL) for users as seen in step two:

= Xml.Tables(Web.Contents("http://********:8080/api/users/", [Headers=[Authorization="ApiKey ad2a1ed1-dec1-4c2b-ba60-5f7397932bee", Accept="application/xml"]]))

 

 

2. New web.content url query with unique code and /metadata/

http://********:8080/api/users/22/queued/D20171031T115921-CBTFFOOKL/metadata/

 

This is the query that returns the error I'm describing.

3. Xml.Tables(Web.Contents("http://*********:8080/api/users/" & Text.From([Table.QueueID]) & "/queued/" & Text.From([Table.ID]) & "/metadata/", [Headers=[Authorization="ApiKey ad2a1ed1-dec1-4c2b-ba60-5f7397932bee", Accept="application/xml"]]))

 

Formula.Firewall: Query 'Merge1' (step 'Added Custom') references other queries or steps, so it may not directly access a data source. Please rebuild this data combination.

 

Step two is where I run into an issue about building a query. I am so close to getting this dashboard data imported but this has had me stumped for over a week! Thank you ahead of time!

 

The error is happening because I cannot combine an external data source with another query. As shown in this blog. I have been trying my best to modify what I have to follow this article but can't seem to get it working.

 

https://www.excelguru.ca/blog/2015/03/11/power-query-errors-please-rebuild-this-data-combination/

8 REPLIES 8
v-yuezhe-msft
Employee
Employee

@soundwave123,

Do mask sensitive data when posting question.

Could you please post the full code of your query in Advanced Editor here so that I can modify it? 'Added Custom' step accounts for the issue based on the above error message.

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

let
    Source = Xml.Tables(Web.Contents("http://************:8080/api/users/", [Headers=[Authorization="ApiKey d1-dec1-4c2b-ba60-5f7397932bee", Accept="application/xml"]])),
    Table0 = Source{0}[Table],
    #"Changed Type" = Table.TransformColumnTypes(Table0,{{"ID", Int64.Type}, {"Type", type text}, {"Name", type text}, {"IsLocked", type logical}, {"LastImport", type datetime}, {"HasIncoming", type logical}, {"HasQueued", type logical}, {"HasCompleted", type logical}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Dynamic Query", each Web.Contents("http://*********:8080/api/users/" & Text.From([ID]) & "/queued/", [Headers=[Authorization="ApiKey d1-dec1-4c2b-ba60-5f7397932bee", Accept="application/xml"]])),
    #"Invoke Custom Function1" = Table.AddColumn(#"Added Custom", "Transform File from ID", each #"Transform File from ID"([Dynamic Query])),
    #"Removed Other Columns1" = Table.SelectColumns(#"Invoke Custom Function1", {"Transform File from ID"}),
    #"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File from ID", Table.ColumnNames(#"Transform File from ID"(#"Sample File"))),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Table Column1",{{"Name", type text}, {"Table", type any}}),
    #"Expanded Table" = Table.ExpandTableColumn(#"Changed Type1", "Table", {"QueueID", "ID", "State"}, {"Table.QueueID", "Table.ID", "Table.State"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Table",{"Name"}),
    #"Removed Top Rows" = Table.Skip(#"Removed Columns",1),
    #"Replaced Value" = Table.ReplaceValue(#"Removed Top Rows",null,"",Replacer.ReplaceValue,{"Table.QueueID"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value",null,"",Replacer.ReplaceValue,{"Table.ID"}),
    #"Replaced Value2" = Table.ReplaceValue(#"Replaced Value1",null,"",Replacer.ReplaceValue,{"Table.State"}),
    #"Removed Blank Rows" = Table.SelectRows(#"Replaced Value2", each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"", null}))),
    #"Second Query" = Table.AddColumn(#"Removed Blank Rows", "SecondQuery", each Xml.Tables(Web.Contents("**********:8080/api/users/" & Text.From([Table.QueueID]) & "/queued/" & Text.From([Table.ID]) & "/metadata/", [Headers=[Authorization="ApiKey d1-dec1-4c2b-ba60-5f7397932bee", Accept="application/xml"]])))
in
    #"Second Query"

Getting the same error. 😞

 

I'm getting the same error. Here is what i put in the first query

 

let
    Source = Xml.Tables(Web.Contents("http://**********:8080/api/users/", [Headers=[Authorization="ApiKey dec1-4c2b-ba60-5f7397932bee", Accept="application/xml"]])),
    Table0 = Source{0}[Table]
in
    Table0

 

And second query

 

let Source = firstquery,
    #"Changed Type" = Table.TransformColumnTypes(firstquery,{{"ID", Int64.Type}, {"Type", type text}, {"Name", type text}, {"IsLocked", type logical}, {"LastImport", type datetime}, {"HasIncoming", type logical}, {"HasQueued", type logical}, {"HasCompleted", type logical}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Dynamic Query", each Web.Contents("http://******:8080/api/users/" & Text.From([ID]) & "/queued/", [Headers=[Authorization="ApiKey 4c2b-ba60-5f7397932bee", Accept="application/xml"]])),
    #"Invoke Custom Function1" = Table.AddColumn(#"Added Custom", "Transform File from ID", each #"Transform File from ID"([Dynamic Query])),
    #"Removed Other Columns1" = Table.SelectColumns(#"Invoke Custom Function1", {"Transform File from ID"}),
    #"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File from ID", Table.ColumnNames(#"Transform File from ID"(#"Sample File"))),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Table Column1",{{"Name", type text}, {"Table", type any}}),
    #"Expanded Table" = Table.ExpandTableColumn(#"Changed Type1", "Table", {"QueueID", "ID", "State"}, {"Table.QueueID", "Table.ID", "Table.State"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Table",{"Name"}),
    #"Removed Top Rows" = Table.Skip(#"Removed Columns",1),
    #"Replaced Value" = Table.ReplaceValue(#"Removed Top Rows",null,"",Replacer.ReplaceValue,{"Table.QueueID"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value",null,"",Replacer.ReplaceValue,{"Table.ID"}),
    #"Replaced Value2" = Table.ReplaceValue(#"Replaced Value1",null,"",Replacer.ReplaceValue,{"Table.State"}),
    #"Removed Blank Rows" = Table.SelectRows(#"Replaced Value2", each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"", null}))),
    #"Second Query" = Table.AddColumn(#"Removed Blank Rows", "SecondQuery", each Xml.Tables(Web.Contents("http://*****:8080/api/users/" & Text.From([Table.QueueID]) & "/queued/" & Text.From([Table.ID]) & "/metadata/", [Headers=[Authorization="ApiKey dec1-4c2b-ba60-5f7397932bee", Accept="application/xml"]])))
in
    #"Second Query"

@soundwave123,

If you create new blank queries for  the Dynamic Query and SecondQuery, and then reference the two queries in the steps below, do you get any error message?

#"Added Custom" = Table.AddColumn(#"Changed Type", "Dynamic Query", each Web.Contents("http://******:8080/api/users/" & Text.From([ID]) & "/queued/", [Headers=[Authorization="ApiKey 4c2b-ba60-5f7397932bee", Accept="application/xml"]]))



    #"Second Query" = Table.AddColumn(#"Removed Blank Rows", "SecondQuery", each Xml.Tables(Web.Contents("http://*****:8080/api/users/" & Text.From([Table.QueueID]) & "/queued/" & Text.From([Table.ID]) & "/metadata/", [Headers=[Authorization="ApiKey dec1-4c2b-ba60-5f7397932bee", Accept="application/xml"]])))



Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

I'm trying to figure out how to build the query but I'm a bit confused since I'm still so new at PowerQuery. I know I've been asking alot of the community with this one and I do appreciate your time and patience. I have been learning so much through your guidance.

@soundwave123,

Do mask sensitive data before uploading scripts.

1. Open a new blank query in Query Editor, then paste the following code to Advanced Editor of it.

let
    Source = Xml.Tables(Web.Contents("http://************:8080/api/users/", [Headers=[Authorization="ApiKey d1-dec1-4c2b-ba60-5f7397932bee", Accept="application/xml"]])),
    Table0 = Source{0}[Table]
 in
    Table0


2. Rename the blank query to firstquery.

3. Open another new blank query in Query Editor and paster the following code to Advanced Editor of it.

let
    Source = firstquery,
    #"Changed Type" = Table.TransformColumnTypes(firstquery,{{"ID", Int64.Type}, {"Type", type text}, {"Name", type text}, {"IsLocked", type logical}, {"LastImport", type datetime}, {"HasIncoming", type logical}, {"HasQueued", type logical}, {"HasCompleted", type logical}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Dynamic Query", each Web.Contents("http://*********:8080/api/users/" & Text.From([ID]) & "/queued/", [Headers=[Authorization="ApiKey d1-dec1-4c2b-ba60-5f7397932bee", Accept="application/xml"]])),
    #"Invoke Custom Function1" = Table.AddColumn(#"Added Custom", "Transform File from ID", each #"Transform File from ID"([Dynamic Query])),
    #"Removed Other Columns1" = Table.SelectColumns(#"Invoke Custom Function1", {"Transform File from ID"}),
    #"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File from ID", Table.ColumnNames(#"Transform File from ID"(#"Sample File"))),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Table Column1",{{"Name", type text}, {"Table", type any}}),
    #"Expanded Table" = Table.ExpandTableColumn(#"Changed Type1", "Table", {"QueueID", "ID", "State"}, {"Table.QueueID", "Table.ID", "Table.State"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Table",{"Name"}),
    #"Removed Top Rows" = Table.Skip(#"Removed Columns",1),
    #"Replaced Value" = Table.ReplaceValue(#"Removed Top Rows",null,"",Replacer.ReplaceValue,{"Table.QueueID"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value",null,"",Replacer.ReplaceValue,{"Table.ID"}),
    #"Replaced Value2" = Table.ReplaceValue(#"Replaced Value1",null,"",Replacer.ReplaceValue,{"Table.State"}),
    #"Removed Blank Rows" = Table.SelectRows(#"Replaced Value2", each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"", null}))),
    #"Second Query" = Table.AddColumn(#"Removed Blank Rows", "SecondQuery", each Xml.Tables(Web.Contents("**********:8080/api/users/" & Text.From([Table.QueueID]) & "/queued/" & Text.From([Table.ID]) & "/metadata/", [Headers=[Authorization="ApiKey d1-dec1-4c2b-ba60-5f7397932bee", Accept="application/xml"]])))
in
    #"Second Query"



If you still get error message, please post it for us to analyze.

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
soundwave123
Frequent Visitor

 

1. Original query to get the unique code (D20171031T115921-CBTFFOOKL) for users as seen in step two:

= Xml.Tables(Web.Contents("http://********:8080/api/users/", [Headers=[Authorization="ApiKey ad2a1ed1-dec1-4c2b-ba60-5f7397932bee", Accept="application/xml"]]))

 

 

2. New web.content url query with unique code and /metadata/

http://********:8080/api/users/22/queued/D20171031T115921-CBTFFOOKL/metadata/

 

This is the query that returns the error I'm describing.

3. Xml.Tables(Web.Contents("http://*********:8080/api/users/" & Text.From([Table.QueueID]) & "/queued/" & Text.From([Table.ID]) & "/metadata/", [Headers=[Authorization="ApiKey ad2a1ed1-dec1-4c2b-ba60-5f7397932bee", Accept="application/xml"]]))

 

Formula.Firewall: Query 'Merge1' (step 'Added Custom') references other queries or steps, so it may not directly access a data source. Please rebuild this data combination.

 

Step two is where I run into an issue about building a query. I am so close to getting this dashboard data imported but this has had me stumped for over a week! Thank you ahead of time!

 

The error is happening because I cannot combine an external data source with another query. As shown in this blog. I have been trying my best to modify what I have to follow this article but can't seem to get it working.

 

https://www.excelguru.ca/blog/2015/03/11/power-query-errors-please-rebuild-this-data-combination/

Anyone have any suggestions? I feel like I know what I need but just don't know how to modify my data requests to accomplish it.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors