Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I am fairly new to Power BI and I am currently exploring whether this is the right tool for me or not. Doing that I have quickly realised that it seems to be very hard to work with three- (or more) dimensional data. I.e. I have a table where one of the columns contains a table for each of the row. In these situations I find it very hard to work with this data and I was wondering what the correct syntax is for this if it is even possible.
Say for instance that I want to add a calculated column to a nested table for every row of my main data-table, how would I go about doing that?
To be slightly more specific, say I have a table where column "SubTable" is a tabular value which has columns "A" and "B", and I want to construct a column "C" = "A + B" for every row of my main table. I am in general just curious about these types of problems. Ideally I would also want to work with four- and five-dimensional data if that is possible. If I try to use the Power Query Editor it seems as if I can only edit the tabular value of a single row.
Solved! Go to Solution.
do you mean something like this?
let Source = #table({"SubTable"}, {{[A=1,B=11]},{[A=2,B=12]}}), #"Added Custom1" = Table.AddColumn(Source, "C", each [SubTable][A]+[SubTable][B]) in #"Added Custom1"
I have the same type of issues it is a bit different. in my case, I import data from MongoDB which has nested tables in each record because of this whenever i import the data into power bi I am facing issues with this data format and building analytics on this data.
do you mean something like this?
let Source = #table({"SubTable"}, {{[A=1,B=11]},{[A=2,B=12]}}), #"Added Custom1" = Table.AddColumn(Source, "C", each [SubTable][A]+[SubTable][B]) in #"Added Custom1"
Thank you, yes that is basically what I was after.
In retrospect I have come to realise that this is not really the correct approach to take in Power BI, and that I should rethink my problem in terms of flat tables if possible. Although, I am also starting to think that this is not really the right tool for my problem.
In order to consume the data you will need to put in flat tables, so with multiple dimensions it may not work out well
you can use Python & R in PowerBI though, so that may be a workaround
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
83 | |
70 | |
60 |
User | Count |
---|---|
150 | |
115 | |
104 | |
89 | |
65 |