Forum Discussion

jcollier's avatar
jcollier
Frequent Visitor
8 years ago
Solved

Create tables based on all Query Parameters

What I have now:

I can edit parameter, choose "1" and I get Luke Skywalker; "2" and I get C-3PO; "3" and it's R2-D2.

It's possible because I have 3 queries:

  1. List of values: { "1", "2", "3" }
  2. Parameter: query from list of values above
  3. Table which changes based on parameter, source: https://swapi.co/people/{parameter}

 

What I'd like to have:

One table with all character details or new table for every character.

 

PBIX:

I can't upload the file. Sorry. Here's how I did it:

  1. List of values: New Source -> Blank Query -> Advanced Editor -> { "1", "2", "3" }
  2. Parameter: Manage Parameters -> Type: text -> Suggested values: query -> Query: list above
  3. Table: New Source -> Web -> Advanced -> URL parts -> 1st part: https://swapi.co/api/people/ and 2nd part: parameter (created above)
Need Help
  • jcollier's avatar
    jcollier
    8 years ago

    I did it in one table instead of creating new table for each character's id.

     

    We need one table and one function.

     

    Here's full solution:

     

    1. Create function getCharacter(id):

     

     

    let
    Source = (id as text) => let
        Source = Json.Document(Web.Contents("https://swapi.co/api/people/" & id)),
        #"Converted to Table" = Record.ToTable(Source)
    in
        #"Converted to Table"
in
    Source

     

    2. Create table with ids for id parameter in URL: https://swapi.co/api/people/{id}

     

    Table with ids for API: https://swapi.co/api/people/{id}

    3. Invoke the function getCharacter(id) on this table: go to 'Add column' -> 'Invoke custom function' and expand.

     

    Expanded table with character details for each id specified earlier

    M code for the expanded table above:

     

    let
    idList = {"1", "2", "3"},
    #"Converted to Table" = Table.FromList(idList, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "id"}}),
    #"Invoked Custom Function" = Table.AddColumn(#"Renamed Columns", "getCharacter", each getCharacter([id])),
    #"Expanded getCharacter" = Table.ExpandTableColumn(#"Invoked Custom Function", "getCharacter", {"Name", "Value"}, {"getCharacter.Name", "getCharacter.Value"})
in
    #"Expanded getCharacter"

5 Replies

  • jcollier's avatar
    jcollier
    Frequent Visitor
    8 years ago

    I did it in one table instead of creating new table for each character's id.

     

    We need one table and one function.

     

    Here's full solution:

     

    1. Create function getCharacter(id):

     

     

    let
    Source = (id as text) => let
        Source = Json.Document(Web.Contents("https://swapi.co/api/people/" & id)),
        #"Converted to Table" = Record.ToTable(Source)
    in
        #"Converted to Table"
in
    Source

     

    2. Create table with ids for id parameter in URL: https://swapi.co/api/people/{id}

     

    Table with ids for API: https://swapi.co/api/people/{id}

    3. Invoke the function getCharacter(id) on this table: go to 'Add column' -> 'Invoke custom function' and expand.

     

    Expanded table with character details for each id specified earlier

    M code for the expanded table above:

     

    let
    idList = {"1", "2", "3"},
    #"Converted to Table" = Table.FromList(idList, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "id"}}),
    #"Invoked Custom Function" = Table.AddColumn(#"Renamed Columns", "getCharacter", each getCharacter([id])),
    #"Expanded getCharacter" = Table.ExpandTableColumn(#"Invoked Custom Function", "getCharacter", {"Name", "Value"}, {"getCharacter.Name", "getCharacter.Value"})
in
    #"Expanded getCharacter"