Log In
Register
Home
March 21, 2019
Offline
Hi,
I have a table. Within the table some of the columns have nested tables within them.
Column "TableFFromlisttest" has a nested table in each row. In that nested table is one column called "Column1".
The data in column 1 of the nested table may vary in each row from ie contain any combinations of the three rows or only one of them
Column "levytable" also has a nested table in each row. In that nested table is two columns "Wording in Regs" & "Levy $"
Somehow I need to combine or lookup or transform so that Column "TableFFromlisttest" nested table contains an added column "$" that pulls data or looks up from the other nested table.
The result would be a nested table Column A in each row.
Any help would be appreciated
Trusted Members
February 13, 2021
Offline
Phil gives a good way to do this in this video. If you need further assistance please upload an example file complete with an example of your desired result. It is helpful to manipulate the data to better understand your issue. Hope this helps.
March 21, 2019
Offline
Hi Jessica,
Thanks for your tips. I tried Phil's method. and it does pull data from a table based on cell containing a 'value'.
However I'm trying to add another column to the nested table in the 'TableFFromlisttest' column which finds the 'Levy $' which I then get the maximum and return as a result the "Wording in Regs"
I've included a PBI file. I can do it by another method but would like to achieve the:
I hope this makes it clearer
screen shots of the other bits
Trusted Members
February 13, 2021
Offline
Unfortunately, I don't have Power BI so I can't see the data. Hopefully, someone else can help you out that can see the data. I'm sorry.
Trusted Members
Moderators
Power BI
January 31, 2022
Offline
Hi Leroy,
I took a look at the pbix file but found it difficult to figure out what you are trying to achieve. Decided to copy the data and queries into Excel and added a few steps to the query that combines the Data and Levies tables before loading it back to Excel. It may or may not be what you want.
If not, can you add a table to the Excel file (by typing) showing us what the end result should be?
Riny
March 21, 2019
Offline
Hi Riny,
Thanks for the reply. I tried to simplify it for better understanding of what I'm after.
[list from now] which contains a nested list or [TableFFromlisttest] which contains a nested table can be used. Either is ok. Because Phil's video suggests using List.PostitionOf is a faster method & I have a lot of data I'm wanting to achieve the result by not using a merge process.
Result I'm after is the nested table has an additional column[Levy $] which has the corresponding levy amount from some kind of a lookup
I'm then able able to then get the record with the maximum levy amount and continue on with further steps.
Trusted Members
Moderators
Power BI
January 31, 2022
Offline
Hi Leroy,
Perhaps I'm just slow today but I still don't get it. You mention that you want "a nested table with a conditional column for Levy $". Isn't that exactly what you already have, as shown in the Result-required picture?
March 21, 2019
Offline
Hi Riny,
Apologies for the late reply. You are correct in that I achieved the result. However I went to and watched the link Jessica mentioned and it seemed to imply that using List.PositionOf was a more efficient/quicker way than using some kind of merge. As I have a large amount of data the current method takes 2.5 hours to update. I was hoping to find another way and then I could test it to see if indeed it was more efficient.
Thanks for you input.
Regards
Leroy
1 Guest(s)
