Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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:
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:
Solved! Go to Solution.
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}
3. Invoke the function getCharacter(id) on this table: go to 'Add column' -> 'Invoke custom function' and expand.
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"
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}
3. Invoke the function getCharacter(id) on this table: go to 'Add column' -> 'Invoke custom function' and expand.
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
@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'.
list | character |
1 | Table |
2 | Table |
3 | Table |
You may write code in The Advanced Editor.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
94 | |
81 | |
66 | |
58 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |