Forum Discussion
Make cross join in M code dynamically
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
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 JsonConvertError 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