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.
I am building a PBI report that has to do with current congressional legislation. The bulk data can all be found here:
https://www.govinfo.gov/bulkdata/BILLSTATUS/117/hr/
I am trying to create a table where each row is a different bill.
This issue is that every individual bill has its own XML web link. For example: HR1 is found at https://www.govinfo.gov/bulkdata/BILLSTATUS/117/hr/BILLSTATUS-117hr1.xml and HR 23 is found at https://www.govinfo.gov/bulkdata/BILLSTATUS/117/hr/BILLSTATUS-117hr23.xml. Each one of these sources is only one row. I am trying to append all of these individual sources to create a table where each row is a different bill.
At the bottom of the page there is a downloadable zip file with all of the bills: https://www.govinfo.gov/bulkdata/BILLSTATUS/117/hr/BILLSTATUS-117-hr.zip
Is there a way to download this zip file directly from the web and extract each individual xml file (bill) onto a different row?
Alternatively, is there a different site that has all this information in the format I am looking for it in?
Solved! Go to Solution.
Refer to this excellent post by @artemus
Solved: Re: How to connect Azure DevOps REST API in to pow... - Microsoft Power BI Community
Take that function, declare it in your Power Query and call it Unzip. Then you can do things like:
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]))
in
#"Added Custom"
to get your indvidual bills
It is already part of the M code above - you get the contents of the XML in the text column. Power Query has an XML Parser. You will need to indicate which leaves you want to read from the XML tree.
Refer to this excellent post by @artemus
Solved: Re: How to connect Azure DevOps REST API in to pow... - Microsoft Power BI Community
Take that function, declare it in your Power Query and call it Unzip. Then you can do things like:
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]))
in
#"Added Custom"
to get your indvidual bills
This is awesome! Thanks!! Now I just need to figure out how to pull XML files out of this zip folder. Any clue how that works?
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.
User | Count |
---|---|
110 | |
94 | |
82 | |
66 | |
58 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |