Using web.content xml to stage data then reference...

soundwave123 · ‎11-14-2017

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/

Anonymous · ‎11-15-2017

@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

soundwave123 · ‎11-16-2017

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"

soundwave123 · ‎11-17-2017

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"

Anonymous · ‎11-20-2017

@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

soundwave123 · ‎11-20-2017

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.

Anonymous · ‎11-17-2017

@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

soundwave123 · ‎11-14-2017