Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
jcollier
Frequent Visitor

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)
1 ACCEPTED SOLUTION
jcollier
Frequent Visitor

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}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 earlierExpanded 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"

View solution in original post

5 REPLIES 5
jcollier
Frequent Visitor

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}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 earlierExpanded 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"

While this is an old post, for anyone who ends up here looking for a solution, the above process is explained quite well in this video:

https://www.youtube.com/watch?v=MCTXMUuH3gk&ab_channel=PowerBIGuy

jcollier
Frequent Visitor

@v-chuncz-msft, thank you. Also, if somebody has similiar problem, that's very helpful: Radacad: Custom Functions Made Easy in Power BI Desktop.

 

Right now, I have a table for each parameter. Below.

 

How can I export Table as a new query automatically?

 

I know I can do it manually: right click on the Table and choose 'Add as a new query'.

 

listcharacter
1Table
2Table
3Table

 

 

@jcollier,

 

You may write code iThe Advanced Editor.

Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
v-chuncz-msft
Community Support
Community Support

@jcollier,

 

You may right click the query and select Create Function, then invoke it for each row in Query Editor.

https://msdn.microsoft.com/en-us/library/mt185361.aspx

Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors