cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
slackerhx Regular Visitor
Regular Visitor

Custom function with rest API - Expand nested tables in columns

Hi All, 

 

I'm currently using Baidu maps distance lite API to estimate the shortest travel time for each Origin to Destination, where the Origin and Destination will change dynamically. 

 

I am trying to achieve as per the screenshot below when one adds column by "Invoke Custom Function" to the source table.

 

From this: 

Before.PNG

 

To this: 

after.PNG

 

the current m code that i have is :

 

(Origin as text, Destination as text) =>

let 
    Baidu_API_key = "XXXX",
    Source = Json.Document(Web.Contents("http://api.map.baidu.com/directionlite/v1/transit?origin="&Origin&"&destination="&Destination&"&ak="&Baidu_API_key)),
    #"Results" = Table.ExpandRecordColumn(Table.FromList(Record.ToList(Record.SelectFields(Source,"result")),Splitter.SplitByNothing(),null,null,ExtraValues.Error),"Column1", {"origin", "destination", "routes", "taxi"}),
    #"Routes" = Table.ExpandListColumn(Table.SelectColumns(#"Results","routes"),"routes"),
    #"Shortest travel" = Table.ExpandRecordColumn(#"Routes", "routes", {"distance", "duration", "price"}),
    #"results in a row" = Table.Min(#"Shortest travel", "duration")
in
    #"results in a row"

Appreciate if any kind folks can advise which functions should I use to achieve the above. 

 

Thanks in advance.

5 REPLIES 5
Super User II
Super User II

Re: Custom function with rest API - Expand nested tables in columns

Hi @slackerhx,

 

in pseudocode it should be as follows. I hope I have no typos in code.

 

let
    Source = <YOUR PREVIOUS STEPS>,
    BaiduResult = Table.AddColumn(Source, "Baidu Result", each yourFunctionName([Origin], [Destinantion])),
    Result = Table.ExpandRecordColumn(BaiduResult, "Baidu Result", {"distance", "duration", "price"})
in
    Result
Highlighted
slackerhx Regular Visitor
Regular Visitor

Re: Custom function with rest API - Expand nested tables in columns

Hi @Nolock

 

I've tried your suggested method but i got the following error:

 

Expression.Error: We cannot convert a value of type Record to type Table.
Details:
Value=Record
Type=Type

 

I've tried to modify the code to 

 

(Origin as text, Destination as text) =>

let 
    Baidu_API_key = "XXXX",
    Source = Json.Document(Web.Contents("http://api.map.baidu.com/directionlite/v1/transit?origin="&Origin&"&destination="&Destination&"&ak="&Baidu_API_key)),
    BaiduResult = Table.AddColumn(Record.ToTable(Source), "Baidu Result", each Custom([Origin], [Destination])),
    Result = Table.ExpandRecordColumn(BaiduResult, "Baidu Result", {"distance", "duration", "price"})
in
    Result

but i got the following error instead: 

 

Expression.Error: The field 'Origin' of the record wasn't found.
Details:
Name=status
Value=0

 

 

Super User II
Super User II

Re: Custom function with rest API - Expand nested tables in columns

Hi @slackerhx,

you've understand me wrong.

Unfortunately I can't prepare a 100 % working solution for you because I don't have any APIkey for Baidu for testing, but I hope you'll see my point in the following code.

 

let
    Source = <YOUR TABLE WITH COLUMNS ORIGIN AND DESTINATION>,

    // your function getting the distance (you can also remove it from this expression and place somewhere else as separated query)
    fnCalculateDistance = (Origin as text, Destination as text) as table =>
        let 
            Baidu_API_key = "XXXX",
            Source = Json.Document(Web.Contents("http://api.map.baidu.com/directionlite/v1/transit?origin="&Origin&"&destination="&Destination&"&ak="&Baidu_API_key)),
            #"Results" = Table.ExpandRecordColumn(Table.FromList(Record.ToList(Record.SelectFields(Source,"result")),Splitter.SplitByNothing(),null,null,ExtraValues.Error),"Column1", {"origin", "destination", "routes", "taxi"}),
            #"Routes" = Table.ExpandListColumn(Table.SelectColumns(#"Results","routes"),"routes"),
            #"Shortest travel" = Table.ExpandRecordColumn(#"Routes", "routes", {"distance", "duration", "price"}),
            #"results in a row" = Table.Min(#"Shortest travel", "duration")
        in
            #"results in a row",

    // add a column to Source where every row contains the result of fnCalculateDistance (the result is a table with one row)
    BaiduResult = Table.AddColumn(Source, "Baidu Result", each fnCalculateDistance([Origin], [Destinantion])),
    // expand the new table into 3 columns
    Result = Table.ExpandRecordColumn(BaiduResult, "Baidu Result", {"distance", "duration", "price"})
in
    Result
slackerhx Regular Visitor
Regular Visitor

Re: Custom function with rest API - Expand nested tables in columns

@Nolock 

 

I'm not sure what I got wrong as I tried inputing what you've written above but i'm still getting errors: 

 

Expression.Error: We cannot convert a value of type Record to type Table. Details: Value=Record Type=Type
 
this is the inputs in the advance editor, is there anyway to make the excel sheets dynamic instead of hardcoding the sheet name?
 
let

 Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],

 fnCalculateDistance = (Origin as text, Destination as text) as table =>

let 
    Baidu_API_key = "XXXXXXX",
    Source = Json.Document(Web.Contents("http://api.map.baidu.com/directionlite/v1/transit?origin="&Origin&"&destination="&Destination&"&ak="&Baidu_API_key)),
    #"Results" = Table.ExpandRecordColumn(Table.FromList(Record.ToList(Record.SelectFields(Source,"result")),Splitter.SplitByNothing(),null,null,ExtraValues.Error),"Column1", {"origin", "destination", "routes", "taxi"}),
    #"Routes" = Table.ExpandListColumn(Table.SelectColumns(#"Results","routes"),"routes"),
    #"Shortest travel" = Table.ExpandRecordColumn(#"Routes", "routes", {"distance", "duration", "price"}),
    #"results in a row" = Table.Min(#"Shortest travel", "duration")
in
    #"results in a row",

        BaiduResult = Table.AddColumn(Source, "Baidu Result", each fnCalculateDistance([Origin], [Destination])),

        Result = Table.ExpandRecordColumn(BaiduResult, "Baidu Result", {"distance", "duration", "price"})
in
    Result
Super User II
Super User II

Re: Custom function with rest API - Expand nested tables in columns

Hi @slackerhx,

I've read the whole code once again but unfortunately I haven't seen any obvious problem. Please test all the parts of the code separately - that's the only way.

 

let
    Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content]
in
    Source

Then the function.

(Origin as text, Destination as text) as table =>

let 
    Baidu_API_key = "XXXXXXX",
    Source = Json.Document(Web.Contents("http://api.map.baidu.com/directionlite/v1/transit?origin="&Origin&"&destination="&Destination&"&ak="&Baidu_API_key)),
    #"Results" = Table.ExpandRecordColumn(Table.FromList(Record.ToList(Record.SelectFields(Source,"result")),Splitter.SplitByNothing(),null,null,ExtraValues.Error),"Column1", {"origin", "destination", "routes", "taxi"}),
    #"Routes" = Table.ExpandListColumn(Table.SelectColumns(#"Results","routes"),"routes"),
    #"Shortest travel" = Table.ExpandRecordColumn(#"Routes", "routes", {"distance", "duration", "price"}),
    #"results in a row" = Table.Min(#"Shortest travel", "duration")
in
    #"results in a row"

And so on.

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power BI Desktop Update - February 2020

Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors
Top Kudoed Authors