TM1 Reat api Mdx Cube data Tranformation

[Anonymous]

Hello Everyone,

I am new to Power Query. I doing the POC on Tm1 Cube data using Mdx rest api. I have got the data in Power BI. But I would like to transform the cube data in below format.

 

}zPBI_Month is Row Dimension.

}zPBI_Accout is Column Dimension. 

}zPBI_Country and }zPBI_Model are Title Dimension. 

Values are cell value.

Currently In PBI I have only records under Axes. 

PBI - Axes data, How to tranform as Column in Table.

This has record data as below information: 

Kindly let me know how to transform the above data. Thanks.

[v-kelly-msft]

Hi @Anonymous ,

I'm a little confused,do you wanna combine data from 3 queries?

 
Best Regards,
Kelly

[Anonymous]

Hello @v-kelly-msft , 

Sorry for the late reply. I have achieved by combining 3 queries into one table but it kind of static. I would like to make it as more dynamic which can be used across all Cube-view. 

Currently I have dimension info under Axes, where

array 0 has Row Dimension info,     

array 1 has Column Dimension info,     

array 2 has Title Dimension info,     

sometimes title dimension wont be there, in such a case its having array 0 and 1. 

From the Source of Rest api, I have set the title dimension in table which is static, how can i make it as dynamic? 

CreatedTable = Table.FromColumns({ {Source[Axes]{2}[Tuples]{0}[Members]{0}[Attributes][L01]}, Source[Cells] } , { Source[Axes]{2}[Hierarchies]{0}[Name], "Cells"} ),

Question 1) Check if whether Axes[2] is available or not, if available then loop through Axes[2] and set the title dimension as table header. 

Question 2) Like that I have to check row and column dimension and do the cross join.  

How can I achieve this?