Help with JSON API import

[Anonymous]

This is my first time working with importing JSON and I'm somewhat new to PBi and could use some help please.

I have an API URL that was provided to me that pulls in quite a bit of data (fields) and I really only need two fields. I have asked, and the provider of the URL is unable to parse or do anything on his end other than provide me the URL so it's up to me to try to get the information I need...which is fine as it will teach me how to work with JSON import.

1)  I assume, to just narrow down the fields I need, I could right-click on the columns I don't need and click "Remove" correct?

2)  Of the two fields that are returned, one is called "endpoint_type" and is one of two values: "AGENT_TYPE_WORKSTATION" or "AGENT_TYPE_SERVER". I need to write a formula (DAX I assume) that will create a new column (so that I can tie this to a SKU column in another table to be something like this if(endpoint_type="AGENT_TYPE_WORKSTATION", "SECaaS - EPPAAS-D", "SECaaS - EPPAAS-S"). Is it as easy as that?

3) This one's  a little more difficult. The field that is being returned that I need is this part of the JSON which returns a list (first screen shot is the portion of the JSON, the second screen shot is the returned data in PBi). I can break out the list (third screen shot) but I get 1 of 3 values either

1. WORKGROUP, customerNameWithExtraInfo
2. customerName
3. blank

I need to be able to, in case "a" retrieve just the customer name (which is equally difficult becuase it might be

1. WORKGROUP, customerName.Non.domain.joined
2. WORKGROUP, customerName.domain.joined

Thus, I need to remove WORKGROUP and remove Non.domain.joined or domain.joined. I realize this will probably be some nested IF statements, but if someone could help point me in the direction of how to write that in PowerQuery Editor I would be forever indebted. 

Thank you SO much,

Rob

FIRST SCREENSHOT

SECOND SCREENSHOT

THIRD SCREENSHOT

[Anonymous]

@PhilipTreacy  & @v-eqin-msft 

Philip - thank you SO much for your detailed explanation. I'm somewhat new to Power BI. I had some health issues which took me away from work - I'm going to look at this this week and I'll either follow-up with some clarifications or close as solved. I cannot thank you enough!

[v-eqin-msft]

Hi @Anonymous ,

Could you tell me if your problem has been solved by @PhilipTreacy 's method? If it is, kindly Accept it as the solution. More people will benefit from it.

Best Regards,
Eyelyn Qin

[PhilipTreacy]

Hi @Anonymous 

1) When you refer to 'field' do you mean a column?  If so then yes, you can click on the column header and then press Delete.

2) This would be written in M (Power Query's language).  DAX is written in Power BI, not the Power Query editor.  You can add a Custom Column (Ribbon : Add Column -> Custom Column) with code something like this

if Text.Contains([endpoint_type] , "WORKSTATION") then "SECaaS - EPPAAS-D" else "SECaaS - EPPAAS-S"

3) You can use a series of Replace values steps to get the Customer Name.  Right click the Column headre and then click on Replace Values.  By replacing WORKSTATION, , Non.domain.joined and then .domin.joined with nothing, you are left with the Customer Name.

Here's the entire code using my sample data

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Added Custom" = Table.AddColumn(Source, "Custom", each if Text.Contains([endpoint_type] , "WORKSTATION") then "SECaaS - EPPAAS-D" else "SECaaS - EPPAAS-S"),
    #"Replaced Value" = Table.ReplaceValue(#"Added Custom","WORKGROUP,","",Replacer.ReplaceText,{"group_name"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value",".Non.domain.joined","",Replacer.ReplaceText,{"group_name"}),
    #"Replaced Value2" = Table.ReplaceValue(#"Replaced Value1",".domain.joined","",Replacer.ReplaceText,{"group_name"})
in
    #"Replaced Value2"

Download my sample Excel workbook to see how this works. The PQ code works the same in Power BI.

Start Table

End Table

If you have issues adapting my code to your real data, please post some of your own sample data here, it's almost always better to provide data than screenshots.

regards

Phil