Search code examples
jsonpowerbim

Expand column with content from another url

I am using the following code in my own powerBI Data connector to get some date from a json document:

{
  "Customers": [
    {
      "CustomerId": "8cd72f16-8d7b-48b0-90d9-71df011502c8",
      "CustomerTitle": "Test Customer",
    }
}

Code:

GetCustomerTable = (url as text) as table =>
    let
        source = Test.Feed(url & "/overview"),
        value = source[Customers],
        toTable = Table.FromList(value, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
        #"expandColumn" = Table.ExpandRecordColumn(toTable, "Column1", {"CustomerId", "CustomerTitle"}, {"CustomerId", "CustomerTitle"}),
        #"ChangedType" = Table.TransformColumnTypes(#"expandColumn",{{"CustomerTitle", type text}, {"CustomerId", type text})
    in
        ChangedType;

The column "CustomerId" referes to another url where the actual data about the customer is available in json format:

URL: /Details/8cd72f16-8d7b-48b0-90d9-71df011502c8

{
  "Category": "B",
}

What is the best approach to use data from another url with the ExpandRecordColumn function?

Solution

  • So what you need is another custom function to obtain the customer details with each CustomerId, as one of the step:

    GetCustomerDetails = (url as text, customer_id as text) =>
let
    Source = Json.Document(Web.Contents(url & "/Details/" & customer_id)),
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"Category"}, {"Category"})
in
    #"Expanded Column"

    And then you can invoke this function in your original code by passing url and the CustomerId column:

    GetCustomerTable = (url as text) as table =>
    let
        source = Test.Feed(url & "/overview"),
        value = source[Customers],
        toTable = Table.FromList(value, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
        "expandColumn" = Table.ExpandRecordColumn(toTable, "Column1", {"CustomerId", "CustomerTitle"}, {"CustomerId", "CustomerTitle"}),
        "ChangedType" = Table.TransformColumnTypes(#"expandColumn",{{"CustomerTitle", type text}, {"CustomerId", type text}),
        #"Invoked Custom Function" = Table.AddColumn(#"ChangedType", "GetCustomerDetails", each GetCustomerDetails("http://testing.com/", [CustomerId]))
    in
        #"Invoked Custom Function"

    You may need to make some adjustments to the code, depending on how it exactly looks like, but I hope you get the point.