Transform Data using M language in Power BI with TM1 POST MDX Query

[Anonymous]

Hello Eveyone, 

As I am new to Power BI, I dont know the possible ways of doing it. 

I have retrieved the Cube data from Cognos TM1 using RESTapi POST MDX query. Now I would like to do the Data Transformation, in Source I have Axes as list(has row and column name) and Cells as List(has cube values) and would like to construct a table with these. 

So tried using 2 queries, where one for Row and column info and other query for data like below

When I Append Queries, it comes with null one after the other. Merge I cant do because of no relatinship between two. I want columns in adjacent side. 

Any help/other approaches are highly appreciated. 

Thanks

[Jimmy801 · Accepted solution]

Hello @Anonymous 

Hope i got you problem right. Check out this solution.

let
    Source = [odata= "smetcasdcle", ID= "27cpxxx", Axes={"Axes1", "Axes2"}, Cells= {1,2}],
    CreateTable = Table.FromColumns
    (
        {
            Source[Axes],
            Source[Cells]
        },
        {"Axes", "Cells"}
    )
in
    CreateTable

Copy paste this code to the advanced editor in a new blank query to see how the solution works. If this solution fits your need, copy and past a part of it and implement it in your query

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

[Jimmy801 · Accepted solution]

Hello @Anonymous 

Hope i got you problem right. Check out this solution.

let
    Source = [odata= "smetcasdcle", ID= "27cpxxx", Axes={"Axes1", "Axes2"}, Cells= {1,2}],
    CreateTable = Table.FromColumns
    (
        {
            Source[Axes],
            Source[Cells]
        },
        {"Axes", "Cells"}
    )
in
    CreateTable

Copy paste this code to the advanced editor in a new blank query to see how the solution works. If this solution fits your need, copy and past a part of it and implement it in your query

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

[Anonymous]

Hello Everyone, 

Thanks for your reply.  @Jimmy801  yes, we both are in same page. I used your code, yes its working as expected.

While coming to the real huge data for example with 5 dimesions in one cube, how to do transformation in PBI. I used Cross join in my mdx query. I would like to have 5 dimensions in table format with exact data. Is it possible? But I dont have much idea of how to transform the cube data.

Any example snippet/suggestions are welcome. 

I am interested in learning M language resource with example. Kindly share that too. 

[Jimmy801]

Hello @Anonymous 

a MDX-statement will output always a table. So if you want to query your datamodel with MDX (I would always recomend DAX), just create your MDX statement, and the result is importet to power BI. You could use the table import assistant of Excels Power Pivot to create the code automatically. This creates a quite OKAY MDX-code by using a GUI. 

Hope that helps

Jimmy

[Jimmy801]

hello @Anonymous 

this part of the code exactly defines your table output, and the content, as you can see in your result, is not that much

body = "{
""MDX"": ""SELECT {[}zPBI_Account].[Gross], [}zPBI_Account].[Price], [}zPBI_Account].[Units]} on 0, {[}zPBI_Country].[Country01]} on 1 FROM [zPBI_SalesCube] WHERE ([}zPBI_Model].[Model1], [}zPBI_Month].[J01])""
}",

so if you need to add other dimensions you have change the MDX-statement. MDX is basically how a pivot-table is constructed, by rows, columns, filters and measures. 

Hope it helps

Jimmy

[Anonymous]

Hello @Jimmy801 

Yes, I understood. 

This was my initial query "body = "{
""MDX"": ""SELECT NON Empty {Crossjoin( [}zPBI_Account].Members], [}zPBI_Country].Members] )} on 0, {[}zPBI_Month].Members]} on 1 FROM [zPBI_SalesCube] WHERE ([}zPBI_Model].[Model1])""
}","

I was trying to take all the values for all dimensions. Due to the huge volumn the values are messed up, so took a very less value for testing. When I give url as http://localhost:8000/api/v1/ExecuteMDX?$expand=Cells($select=Ordinal,Value) then 

I get the exact data. My aim to get all the dimesions information and country-wise, month wise and have to do visualization. What could be the best way to achieve this. 

[Jimmy801]

Hello @Anonymous 

you can add these needed dimensions into the mdx-statement

BR

Jimmy

[Anonymous · Accepted solution]

Hello @Jimmy801 am very new to Power BI , 

Here is the cube for testing,

Here is my query:

let
url = "http://localhost:8000/api/v1/ExecuteMDX?$expand=Axes($select=Ordinal;$expand=Tuples($count;$expand=Members($select=Name))),Cells($select=Ordinal,Value)",

body = "{
""MDX"": ""SELECT {[}zPBI_Account].[Gross], [}zPBI_Account].[Price], [}zPBI_Account].[Units]} on 0, {[}zPBI_Country].[Country01]} on 1 FROM [zPBI_SalesCube] WHERE ([}zPBI_Model].[Model1], [}zPBI_Month].[J01])""
}",

Source = Json.Document(Web.Contents(url, [Headers=[#"Authorization"="Basic YYY", #"Content-Type"="application/json"], Content = Text.ToBinary(body)

])),
CreatedTable = Table.FromColumns({ Source[Axes], Source[Cells] }, {"Axes", "Cells"} ),

 #"Expanded Axes" = Table.ExpandRecordColumn(CreatedTable, "Axes", {"Ordinal", "Tuples@odata.count", "Tuples"}, {"Axes.Ordinal", "Axes.Tuples@odata.count", "Axes.Tuples"}),
#"Expanded Axes.Tuples" = Table.ExpandListColumn(#"Expanded Axes", "Axes.Tuples"),
#"Expanded Axes.Tuples1" = Table.ExpandRecordColumn(#"Expanded Axes.Tuples", "Axes.Tuples", {"Ordinal", "Members"}, {"Axes.Tuples.Ordinal", "Axes.Tuples.Members"}),
#"Expanded Axes.Tuples.Members" = Table.ExpandListColumn(#"Expanded Axes.Tuples1", "Axes.Tuples.Members"),
#"Expanded Axes.Tuples.Members1" = Table.ExpandRecordColumn(#"Expanded Axes.Tuples.Members", "Axes.Tuples.Members", {"Name"}, {"Axes.Tuples.Members.Name"}),
#"Expanded Cells" = Table.ExpandRecordColumn(#"Expanded Axes.Tuples.Members1", "Cells", {"Ordinal", "Value"}, {"Cells.Ordinal", "Cells.Value"}),
#"Reordered Columns" = Table.ReorderColumns(#"Expanded Cells",{"Axes.Tuples.Ordinal", "Axes.Ordinal", "Axes.Tuples@odata.count", "Axes.Tuples.Members.Name", "Cells.Ordinal", "Cells.Value"}),
#"Removed Columns" = Table.RemoveColumns(#"Reordered Columns",{"Axes.Tuples.Ordinal", "Axes.Ordinal", "Axes.Tuples@odata.count", "Cells.Ordinal"})
in
#"Removed Columns"

Output in PBI: 

Dont know how to get the exact cube format. Even I doubted my approach will work for large cubes.:( 

[HotChilli]

If i understand you correctly, you want a new table with 2 columns.  These columns come from your 2 existing tables and you want them to merge together in the order that they they are currently in.

I think you could add an index column to each table (from Add Column menu) then do a 'merge as new' with both tables (using an inner join on the Index column).

Expand the table and remove the index columns as required.