cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
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
Super User

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
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
Super User

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
Super User

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
October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Badges

Incoming: New and Improved Badges

Exciting news: We've given our badges an overhaul and added brand news ones.

Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Top Kudoed Authors
Users Online
Currently online: 337 members 3,467 guests
Please welcome our newest community members: