Showing results for 
Search instead for 
Did you mean: 
Frequent Visitor

Filling a field from another field in a table

I am trying to fill a field with data from another table - however when I do this using Table[Field] in Custom Column my file goes into meltdown and turns from a 500kb file to one of many many Mb - so must be doing something wrong.


So I have a table of invoices which show the Date Due and the period in which the invoice was raised. The data table is an historical data snapshot - so each period I get new data. But rather than hard-coding when the data relates to I want to use a query to work it out. (also need to learn how to post nice data!)


Below is a very brief extract of the tables - this is the  main data table

Period     Customer    Due Date    Amount     Calc    Days  Over



I then have a table which will give me the effective due date based on the Period


Period     Abs Due Date



So Looking at my main data table I can see that the most recent period for which I have data is 202102 - so that tells me I need to use 31/05/2021 as my Calc date - I get this by duplicating my data table, stripping out all columns except for period, remove duplicates, sort by Period and remove all but the first entry - so I get the following table




What I was doing was then adding this field to my main data table (for all rows) using a custom column and then trying to use a Merge to bring in the Abs Due Date into the Table so I can then calculate exactly how many days over each invoice is by subdtracting the Abs Due Date form the Due Date


But when I do this my Query goes into a melt down - takes for ever to evaluate and grows into many many MB!


I think what I am doing is quite simple - but clearly I am going about it wrong - still a relative newbie!!

Frequent Visitor

Thanks - I will give both a go and see what it does to the memory and size of the file

Hi @Calvinorr 

Does my solution work? Can you share some feedback? 

Community Support
Community Support

Hi @Calvinorr 


I'm not sure if I understand your expected outcome correctly, so I will provide two solutions below based on two scenarios. I think the latter one is probably what you want. 


Scenario One: 

If you want to bring Abs Due Date from Effective Due Dates table into Main data table based on every period, you can use Merge Queries feature. Steps are as below. 





Finally add a custom column to calculate over days. 


Duration.Days([Due Date] - [Abs Due Date])




Scenario Two:

When you want to get the latest Abs Due Date value for all rows in Main data table.

In Effective Due Data table, sort by Period column descendingly, then keep the top one row. You will get the latest row.



In Main data table, add a custom column with below code. 

List.First(Table.Column(#"Effective Due Dates (2)","Abs Due Date"))



Finally add a custom column to get over days with the same method in Scenario One. 



Both samples are included in the attached pbix file. Hope it helps!


Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

Super User
Super User

Seems like you could just use Table.NestedJoin (PriorStepOrTableName, {Period}, OtherTableName, {Period}, "TableValues", JoinKind.LeftOuter)




I’m usually answering from my phone, which means the results are visualized only in my mind. You’ll need to use my answer to know that it works—but it will work!!

Helpful resources

PBI Sept Update Carousel

Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

Learn Live

Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

Top Solution Authors
Top Kudoed Authors