Forum Discussion
Using web.content xml to stage data then reference it for multiple queries
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
- soundwave1238 years agoFrequent Visitor
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.