cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Irubataru Frequent Visitor
Frequent Visitor

Working with tables inside of tables

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Stachu Super Contributor
Super Contributor

Re: Working with tables inside of tables

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"
Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

3 REPLIES 3
Stachu Super Contributor
Super Contributor

Re: Working with tables inside of tables

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"
Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Irubataru Frequent Visitor
Frequent Visitor

Re: Working with tables inside of tables

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.

Stachu Super Contributor
Super Contributor

Re: Working with tables inside of tables

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

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 207 members 2,245 guests
Please welcome our newest community members: