cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted

Make cross join in M code dynamically

Hello Everyone, 

 

I have a table as below: 

OriginalTable.png

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: 

crossjoin-output.png

 

Is it possible to achieve?

 

Thanks

 

2 ACCEPTED SOLUTIONS

Accepted Solutions
Microsoft artemus
Microsoft

Re: 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
   CrossJoin

 

Your results will depend on how the Rest call returns results.

View solution in original post

Re: Make cross join in M code dynamically

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

View solution in original post

7 REPLIES 7
Super User III
Super User III

Re: Make cross join in M code dynamically

Dynamic URLs won't refresh in the service unfortunately.

 

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark my post as a solution. Thanks!

Imke Feldmann

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

 
Microsoft artemus
Microsoft

Re: Make cross join in M code dynamically

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.

Re: Make cross join in M code dynamically

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, 

OriginalTable.png

 

From this input, I have to achieve below:

 

crossjoin-output.png 

 

Best regards.

Microsoft artemus
Microsoft

Re: 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
   CrossJoin

 

Your results will depend on how the Rest call returns results.

View solution in original post

Re: Make cross join in M code dynamically

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

 

Microsoft artemus
Microsoft

Re: Make cross join in M code dynamically

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

 

Re: Make cross join in M code dynamically

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

View solution in original post

Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Top Solution Authors
Top Kudoed Authors