Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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/
@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
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
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
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.