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' va...
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
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