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.
Hello Everyone,
I have a table as below:
I would like to make rest api request to get the list of elements for each row and do cross join dynamically.
For example, '}zPBI_account' value pass to query which returns element list and similarly pass '}zPBI_Model' to the same query and returns elements list. By having both elements list I have to cross join.
Expected outcome should be:
Is it possible to achieve?
Thanks
Solved! Go to Solution.
If you need to make a REST call you can do something like:
let
Source = List.Select(DimensionName[Name], each [Name=_, Data=Web.Contents("https://myurl.com/GetDimensions?Dimension=" & _)]),
JsonConvert = List.Select(Source, each Json.Document([Data])[<<Drill into Json here>>]),
AddCOlumns= List.Accumulate(JsonConvert, each #table(type table [], {}), (current, next) => Table.AddColumn(current, next[Name], each next[Data])),
CrossJoin = List.Accumulate(Table.ColumnNames(AddColumns), AddColumns, (current, next) => Table.ExpandListColumn(current, next))
in
CrossJoin
Your results will depend on how the Rest call returns results.
Hi @artemus,
Thanks for your reply. Perfect Solution.
Its working as expected, but I did the below minor changes in your statement.
= List.Accumulate(JsonConvert, each #table(type table [], { }), (current, next) => Table.AddColumn(current, next[Name], each next[Elements]))
>>>>>>>> removed each and added {} in table syntax>>>>>>>>>>>>>>
= List.Accumulate(JsonConvert, #table(type table [], { {} }), (current, next) => Table.AddColumn(current, next[Name], each next[Elements]))
Regards
What do you mean by dynamically?
To do a cross join you can:
1. Reference Query1 in a new query
2. Add a custom colum with defination: Query2
3. Expand the new custom column
If you want tto make web requests when a user is using it, you have to use the Power App visual.
Thanks for your reply.
Hi @ImkeF,
I am using query for extracting the data in Advanced Editor. May be I wrongly used the word dynamic in PBI. The Initial table below with dimensionName may grow bigger or smaller based on input cube. Thatsy meant the word dynamic. If I hae 5 dimension, then get elements for all 5 dimension and have to cross join for 5 dimension.
Hi @artemus,
I am working on Cube data. I have a table, which has DimensionName. Using the DimensionName I have to make a rest api call to get the list of elements for each dimension and cross join all dimension elements.
This is the initial table with list of DimensionName,
From this input, I have to achieve below:
Best regards.
If you need to make a REST call you can do something like:
let
Source = List.Select(DimensionName[Name], each [Name=_, Data=Web.Contents("https://myurl.com/GetDimensions?Dimension=" & _)]),
JsonConvert = List.Select(Source, each Json.Document([Data])[<<Drill into Json here>>]),
AddCOlumns= List.Accumulate(JsonConvert, each #table(type table [], {}), (current, next) => Table.AddColumn(current, next[Name], each next[Data])),
CrossJoin = List.Accumulate(Table.ColumnNames(AddColumns), AddColumns, (current, next) => Table.ExpandListColumn(current, next))
in
CrossJoin
Your results will depend on how the Rest call returns results.
Hi @artemus,
Thanks for your reply.
let
DimensionName = #table
(
{"Name"},
{
{"}zPBI_Account"}, {"}zPBI_Month"}
}
),
Source = List.Select(DimensionName[Name], each [Name=_, Data=Web.Contents("http://localhost:8000/api/v1/Dimensions('"& _ &"')/Hierarchies('"& _ &"')?$expand=Elements($select=Name) ", [Headers=[Authorization="Basic XXXXX", ContentType="application/json; charset=utf-8"]])]),
JsonConvert = List.Select(Source, each Json.Document([Data]))
in
JsonConvert
Error as below :
Expression.Error: We cannot convert a value of type Record to type Logical.
Details:
Value=[Record]
Type=[Type]
One more problem is 'authorization' header is only supported when connecting anonymously. 😞 I cant edit the request as its not showing in 'Data Source settings'.
Oops, I put that together in a bit of a hurry.
Replace List.Select with List.Transform (both times).
Hopefully the data source thing should go away once you make that change.
Oh and if you just need those two values, you can replace DimensionName[Name] with simply
{"}zPBI_Account"}, {"}zPBI_Month"}
Hi @artemus,
Thanks for your reply. Perfect Solution.
Its working as expected, but I did the below minor changes in your statement.
= List.Accumulate(JsonConvert, each #table(type table [], { }), (current, next) => Table.AddColumn(current, next[Name], each next[Elements]))
>>>>>>>> removed each and added {} in table syntax>>>>>>>>>>>>>>
= List.Accumulate(JsonConvert, #table(type table [], { {} }), (current, next) => Table.AddColumn(current, next[Name], each next[Elements]))
Regards
Dynamic URLs won't refresh in the service unfortunately.
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
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 |
---|---|
102 | |
53 | |
21 | |
12 | |
12 |