REST API - Data pulled as BINARY??

[Sankzpower]

Hi guys

I am struggling to get Power bi work. I have the API call which works perfectly so all the ID has value attached by yearly level. I have a table of multiple IDs and can successfully pull data by making a call individually. But to pull the data all together I have added a custom column to retrieve all data however it comes out as "Binary" (Not sure where do I go from here). Once I clicked manually, it goes in with further data on what I need but I would have to click individually. 

Anyone can suggest where do I go from here, I have attached this link to download the word file where I have posted the images. 

It probably a simple solution but any help would be appreciated. 

Thanks in advance

[dax · Accepted solution]

Hi tsankar, 

When the content contain nested table or record should be like below. I think you could try ti pivot the table , then expand the records to get your data. You could refer to below M code to see whether it work or not.

let
    Source = [filed=1,name="a", date=2019/1/1, cc=[a=1,b=2]],
    #"Converted to Table" = Record.ToTable(Source),
    #"Pivoted Column" = Table.Pivot(#"Converted to Table", List.Distinct(#"Converted to Table"[Name]), "Name", "Value"),
    #"Expanded cc" = Table.ExpandRecordColumn(#"Pivoted Column", "cc", {"a", "b"}, {"cc.a", "cc.b"})
in
    #"Expanded cc"

Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.