Make cross join in M code dynamically

[Anonymous]

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

[artemus · Accepted 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.

[artemus]

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.

[Anonymous]

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.

[artemus · Accepted 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.

[Anonymous]

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

[artemus]

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"}

[Anonymous · Accepted solution]

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

[ImkeF]

Dynamic URLs won't refresh in the service unfortunately.