Filling a field from another field in a table

[Calvinorr]

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

202102	Abcde	02/01/2022	1000
202101	Abcde	01/01/2021	500
202003	Abcde	03/01/2022	500

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

Period     Abs Due Date

202102	31/05/2021
202101	30/04/2021
202012	31/03/2021

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

Period

202102

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!!

[Calvinorr]

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

[v-jingzhang]

Hi @Calvinorr 

Does my solution work? Can you share some feedback? 

[v-jingzhang]

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.

[watkinnc]

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

--Nate