Forum Discussion
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
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,
Lydialet
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"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
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.
