Unable to Combine Data Refresh Error

[KelliKnitsAlot]

We have a report that pulls multiple data sources from the web to get data on the US Congress (list of members, bills, etc.). To get a complete picture (i.e. a bill number and a bill name), I must merge data sources in Power Query and apply some steps. It refreshes in the desktop fine, but in the service I get the following error:

Processing error: [Unable to combine data] Section1/House Bills/Added Custom1 references other queries or steps, so it may not directly access a data source. Please rebuild this data combination.
Cluster URI: WABI-US-GOV-VIRGINIA-redirect.analysis.usgovcloudapi.net
Activity ID: 3deb3c29-c02c-4d11-ade3-393edf8e961d
Request ID: 8c192f20-5d7d-4421-ef4b-476442aa6fe3
Time: 2021-09-13 11:45:20Z

I have tried changing the privacy settings, moving the merge further up in the M query, joining the opposite way, and the refresh still fails, or the data is incorrect.

The M Query is here:

let
Source = Unzip(Web.Contents("https://www.govinfo.gov/bulkdata/BILLSTATUS/117/hr/BILLSTATUS-117-hr.zip")),
#"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"FileName", "Content"}, {"FileName", "Content"}),
#"Added Custom" = Table.AddColumn(#"Expanded Column1", "Custom", each Text.FromBinary([Content])),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"FileName", "Content"}),
#"Duplicated Column" = Table.DuplicateColumn(#"Removed Columns", "Custom", "Custom - Copy"),
#"Duplicated Column2" = Table.DuplicateColumn(#"Duplicated Column", "Custom - Copy", "Custom - Copy - Copy"),
#"Extracted Text Between Delimiters" = Table.TransformColumns(#"Duplicated Column2", {{"Custom", each Text.BetweenDelimiters(_, "<billNumber>", "</billNumber>"), type text}}),
#"Extracted Text Between Delimiters1" = Table.TransformColumns(#"Extracted Text Between Delimiters", {{"Custom - Copy", each Text.BetweenDelimiters(_, "<latestTitle>", "</latestTitle>"), type text}}),
#"Duplicated Column1" = Table.DuplicateColumn(#"Extracted Text Between Delimiters1", "Custom - Copy - Copy", "Custom - Copy - Copy - Copy"),
#"Extracted Text Between Delimiters3" = Table.TransformColumns(#"Duplicated Column1", {{"Custom - Copy - Copy - Copy", each Text.BetweenDelimiters(_, "<billSummaries>", "</billSummaries>"), type text}}),
#"Renamed Columns" = Table.RenameColumns(#"Extracted Text Between Delimiters3",{{"Custom", "BillNumber"}, {"Custom - Copy", "Bill Name Status"}, {"Custom - Copy - Copy - Copy", "Summaries"}}),
#"Merged Queries" = Table.NestedJoin(#"Renamed Columns", {"BillNumber"}, HouseBillTitles, {"Bill Number"}, "HouseBillTitles", JoinKind.LeftOuter),
#"Expanded HouseBillTitles" = Table.ExpandTableColumn(#"Merged Queries", "HouseBillTitles", {"Bill Title"}, {"HouseBillTitles.Bill Title"}),
#"Renamed Columns2" = Table.RenameColumns(#"Expanded HouseBillTitles",{{"HouseBillTitles.Bill Title", "Bill Name Summary"}}),
#"Added Conditional Column1" = Table.AddColumn(#"Renamed Columns2", "Bill Name", each if [Bill Name Summary] <> null then [Bill Name Summary] else [Bill Name Status]),
#"Added Conditional Column" = Table.AddColumn(#"Added Conditional Column1", "Name Filter", each if Text.Contains([Bill Name], "Inspector General") then 1 else if Text.Contains([Bill Name], "USPS") then 1 else if Text.Contains([Bill Name], "Postal Service") then 1 else if Text.Contains([Bill Name], "Post Office") then 1 else if Text.Contains([Bill Name], "Federal work force") then 1 else if Text.Contains([Bill Name], "IG") then 1 else if Text.Contains([Bill Name], "DEJOY") then 1 else 0),
#"Filtered Rows1" = Table.SelectRows(#"Added Conditional Column", each ([Name Filter] = 1)),
#"Extracted Text Between Delimiters2" = Table.TransformColumns(#"Filtered Rows1", {{"Custom - Copy - Copy", each Text.BetweenDelimiters(_, "<sponsors>", "</cosponsors>"), type text}}),
#"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Extracted Text Between Delimiters2", {{"Custom - Copy - Copy", Splitter.SplitTextByDelimiter("</bioguideId>", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Custom - Copy - Copy"),
#"Extracted Text After Delimiter" = Table.TransformColumns(#"Split Column by Delimiter", {{"Custom - Copy - Copy", each Text.AfterDelimiter(_, ">", {0, RelativePosition.FromEnd}), type text}}),
#"Renamed Columns1" = Table.RenameColumns(#"Extracted Text After Delimiter",{{"Custom - Copy - Copy", "SponsorID"}}),
#"Removed Other Columns" = Table.SelectColumns(#"Renamed Columns1",{"BillNumber", "SponsorID", "Bill Name"}),
#"Cleaned Text" = Table.TransformColumns(#"Removed Other Columns",{{"Bill Name", Text.Clean, type text}}),
#"Trimmed Text" = Table.TransformColumns(#"Cleaned Text",{{"Bill Name", Text.Trim, type text}}),
#"Cleaned Text1" = Table.TransformColumns(#"Trimmed Text",{{"SponsorID", Text.Clean, type text}}),
#"Trimmed Text1" = Table.TransformColumns(#"Cleaned Text1",{{"SponsorID", Text.Trim, type text}}),
#"Filtered Rows" = Table.SelectRows(#"Trimmed Text1", each ([SponsorID] <> "")),
#"Added Custom1" = Table.AddColumn(#"Filtered Rows", "Body", each "House")
in
#"Added Custom1"

The query I am merging uses this as a source: 

Source = Unzip(Web.Contents("https://www.govinfo.gov/bulkdata/BILLSUM/117/hr/BILLSUM-117-hr.zip"))

I am thinking I might have to reference or duplicate this second query and use that to merge, but I was hoping for a solution that did not add more queries to the report.

Any assistance or advice would be appreciated.

[v-angzheng-msft]

Hi, @KelliKnitsAlot 

You need to combine all the queries into one query to work properly. In your case, you need to combine the query of the custom function Unzip into the main query.

You can refer to these two posts to combine your queries.

[unable to combine data] Please rebuild this data combination - Invoking custom function

Unable to combine data...AutoRemovedColumns1...Please rebuild this data combination

See the links below for more information:

Formula.Firewall: Query references other queries, so it may not directly access a data source.

[Unable to combine data] accessing data sources that have privacy levels

Power Query Errors: Please Rebuild This Data Combination

Hope this helps.

Best Regards,
Community Support Team _ Zeon Zheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[KelliKnitsAlot · Accepted solution]

Thanks. I was able to fix it by adding additional an additional conditional column in the query and then using RELATED DAX functions.  Not the most elegant situation, but it was the only way to get the correct data the way the client wanted it. Thanks for the replies! 

[GilbertQ]

Hi @KelliKnitsAlot 

What I have found to get this working is you have to have all the steps within a single table. As soon as they are in seperate tables you can start to run into issues as you have described.

This does get rather complex and can take some time to get it working.

Here is a blog post that might help: Power Query Errors: Please Rebuild This Data CombinationThe Excelguru Blog

[KelliKnitsAlot]

Thanks. I was able to fix it by adding additional an additional conditional column in the query and then using RELATED DAX functions.  Not the most elegant situation, but it was the only way to get the correct data the way the client wanted it. Thanks for the replies!