Power BI and ABN Searches

[1793aib]

I have an API connection to a CRM, that contains business's Australian Business Number (ABNs).

I want to be able to query the ABN on the ABN Search Website, to bring back the business Name, and other registration details.

Is this something that Power BI / Query can do? Or do I need to get an external source to do this for me?

[ShazaibShoukath]

How to display ABN website or webpage in Power BI ? I'm new here please help me on this ! 

if i use the html contact its shows an error like "abr.business.gov.au refused to connect"

[lbendlin]

Power BI is a reporting tool, not a web wrapper.

[ShazaibShoukath]

Do we have any workaround on this ? or we can't display abr.business.gov.au website ?

[lbendlin]

Use links instead.

[ShazaibShoukath]

Ibendlin I'm new bee here just started to Power BI 1 week before so would you guide which links are you talking about ?? As I see I have used the Iframe link but they don't connect into the website  (refer below screenshot)

If i do load other website it loads prefectly fine but the "abr.business.gov.au" website does'nt load (refer below screenshot)

[lbendlin]

"instead" means that you need to accept that it's not possible because they actively refuse to be shown in an iframe. Use links that open a new browser window.

[Syndicate_Admin]

Hi all, I'm in the same boat as @gourangshah24.  Any help you can provide @lbendlin would be so appreciated, thank you!

[1793aib]

11 060 132 514
11 064 631 001
11 801 702 035
12 112 577 232
12 367 156 765

[lbendlin]

it takes up to five working days to register?

[1793aib]

We are already registered with the ATO for this service, so I've gut a GUID for it. If that's what you mean.

[lbendlin]

yes, but you shouldn't really share that with some random person on the interwebs?

[1793aib]

Agreed.

[lbendlin]

Ok, got my approval. The next step would be for you to indicate how you want the XML flattened out.  Here is a sample response:

<ABRPayloadSearchResults xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://abr.business.gov.au/ABRXMLSearch/">
<request>
<identifierSearchRequest>
<authenticationGUID>87f6ff39-474f-4676-9afc-dc55a66cd71a</authenticationGUID>
<identifierType>ABN</identifierType>
<identifierValue>11 060 132 514</identifierValue>
<history>N</history>
</identifierSearchRequest>
</request>
<response>
<usageStatement>The Registrar of the ABR monitors the quality of the information available on this website and updates the information regularly. However, neither the Registrar of the ABR nor the Commonwealth guarantee that the information available through this service (including search results) is accurate, up to date, complete or accept any liability arising from the use of or reliance upon this site.</usageStatement>
<dateRegisterLastUpdated>2022-01-14</dateRegisterLastUpdated>
<dateTimeRetrieved>2022-01-14T23:56:21.0032296+11:00</dateTimeRetrieved>
<businessEntity202001>
<recordLastUpdatedDate>2000-07-01</recordLastUpdatedDate>
<ABN>
<identifierValue>11060132514</identifierValue>
<isCurrentIndicator>Y</isCurrentIndicator>
<replacedFrom>0001-01-01</replacedFrom>
</ABN>
<entityStatus>
<entityStatusCode>Active</entityStatusCode>
<effectiveFrom>2000-03-11</effectiveFrom>
<effectiveTo>0001-01-01</effectiveTo>
</entityStatus>
<ASICNumber>060132514</ASICNumber>
<entityType>
<entityTypeCode>PRV</entityTypeCode>
<entityDescription>Australian Private Company</entityDescription>
</entityType>
<goodsAndServicesTax>
<effectiveFrom>2000-07-01</effectiveFrom>
<effectiveTo>0001-01-01</effectiveTo>
</goodsAndServicesTax>
<mainName>
<organisationName>DEEPSPRINGS HOLDINGS PTY. LIMITED</organisationName>
<effectiveFrom>2000-05-21</effectiveFrom>
</mainName>
<mainBusinessPhysicalAddress>
<stateCode>NSW</stateCode>
<postcode>2325</postcode>
<effectiveFrom>2000-03-11</effectiveFrom>
<effectiveTo>0001-01-01</effectiveTo>
</mainBusinessPhysicalAddress>
</businessEntity202001>
</response>
</ABRPayloadSearchResults>

Which of the fields are important to you and how do you want to represent them?  XML is by design a ragged hierarchy, but Power Query at the end needs to produce a flat table. So you will need to compromise.

Here is the adjusted code so far.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("NcrBCcAwDEPRVUTOPUh2bHeXkP3XqCHNQXx4aK0hgUnIDaE59vPTRHqXuvRSKBroccigXlTB3C55FhSJyn7tDw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ABN = _t]),
    #"Added Custom" = Table.AddColumn(Source, "Data", each Web.Contents(URL,[RelativePath=Path, Query= [searchString = [ABN],includeHistoricalDetails="N",authenticationGuid=GUID]])),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "XML", each Xml.Document([Data])[Value]{0}{1}[Value]{3}[Value]),
    #"Expanded XML" = Table.ExpandTableColumn(#"Added Custom1", "XML", {"Name", "Value"}, {"Name", "Value"})
in
    #"Expanded XML"

As before provide your own GUID.  The other two parameter have changed slightly:

URL:
"https://abr.business.gov.au/abrxmlsearch" meta [IsParameterQuery=true, Type="Text", IsParameterQueryRequired=true]

Path:
"AbrXmlSearch.asmx/SearchByABNv202001" meta [IsParameterQuery=true, Type="Text", IsParameterQueryRequired=true]

[lbendlin · Accepted solution]

Here is a proposal for a flattening of the XML.  Thanks to @ImkeF for providing guidance.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("NcrBCcAwDEPRVUTOPUh2bHeXkP3XqCHNQXx4aK0hgUnIDaE59vPTRHqXuvRSKBroccigXlTB3C55FhSJyn7tDw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ABN = _t]),
    #"Added Custom" = Table.AddColumn(Source, "Data", each Web.Contents(URL,[RelativePath=Path, Query= [searchString = [ABN],includeHistoricalDetails="N",authenticationGuid=GUID]])),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "XML", each Xml.Document([Data])[Value]{0}{1}[Value]{3}[Value]),
    #"Expanded XML" = Table.ExpandTableColumn(#"Added Custom1", "XML", {"Name", "Value"}, {"Attribute", "Value"}),
    #"Replaced Errors" = Table.TransformColumns(#"Expanded XML", {"Value", each if Value.Is(_, type table) then _ else #table({"Value"}, {{_}}) }),
    #"Expanded Value" = try Table.ExpandTableColumn(#"Replaced Errors", "Value", {"Name",  "Value"}, {"SubAttribute",  "Value"}) otherwise [Value],
    #"Removed Other Columns" = Table.SelectColumns(#"Expanded Value",{"ABN", "Attribute", "SubAttribute", "Value"})
in
    #"Removed Other Columns"

[lbendlin]

I'll let you know if/when I get approved. Here is the beginning of the code that you would use:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("NcrBCcAwDEPRVUTOPUh2bHeXkP3XqCHNQXx4aK0hgUnIDaE59vPTRHqXuvRSKBroccigXlTB3C55FhSJyn7tDw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ABN = _t]),
    #"Added Custom" = Table.AddColumn(Source, "Data", each Web.Contents(URL,[RelativePath=Path, Query= [searchString = [ABN],includeHistoricalDetails="N",authenticationGuid=GUID]]))
in
    #"Added Custom"

You need to create a couple of parameters:

URL:

"https://abr.business.gov.au" meta [IsParameterQuery=true, Type="Text", IsParameterQueryRequired=true]

Path:
"/AbrXmlSearch.asmx/SearchByABNv202001" meta [IsParameterQuery=true, Type="Text", IsParameterQueryRequired=true]

GUID:
"someguid" meta [IsParameterQuery=true, Type="Text", IsParameterQueryRequired=true]

[1793aib]

Thank you! That's working well now.

I'll do some digging on how to reference my other table, but looking good.

[gourangshah24]

Hey mate, i am in similar situation and need help with this. I want abn checked from online abr website to my abn supplier master database. I'm not able to understand what data source to select & then how to load it in powerbi. I have got GUID. Please help with a step by step guide if possible. Highly appreciated if can help.

thanks

[1793aib]

[1793aib]

That is correct. I'm pulling the list of ABNs I want to look up from a CRM into Power BI. I then want to take those ABNs and one by one, search the website for the data relating to those ABNs.

I don't know how to use Web.Contents(), perhaps this is an upwork type of project someone can help me with.

[lbendlin]

Can you share a couple of sample ABNs?