Forum Discussion
Make cross join in M code dynamically
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
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 CrossJoinYour 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
7 Replies
Dynamic URLs won't refresh in the service unfortunately.
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 CrossJoinYour results will depend on how the Rest call returns results.
