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

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

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"