Power Query Transform & Table Formulas not propagating to new records brought in

[collin8579]

Hello,
I Have a query that combines 6 connection only data sets, removes a bunch of columns, moves them around, changes all "Dates" to date code, etc. Nothing out of the ordinary.

In the resulting table/tab "Combined data", I add a number of other columns with formulas that grab/change some of the data based on lookups from other sheets, conditional date checking, etc.

My problem is that when the query brings in new rows, the date format (From within the transform query) and the formulas in the extra columns don’t seem to propagate down without direct interaction.

When I get new rows in the dates end up in the shown format below, 43529.

My two questions: How can I get the dates to propogate down properly?
In the transform part of the query I do assign those columns to date format, so i'm not sure why the new rows aren't catching that.

And: The formulas in the following columns aren't pulling down either, is there a known fix for that?

Any help would be appreciated, as doing all the updates manually defeats the purpose of the query/transform.

Thank you, Collin

 

[cyborgski]

Hi Collin,

Make sure that your columns FCST RE 103 and ACT RE 103 were changed type to date in your Get Data & Transform.

Try this link to debug the formulas not filling down correctly.
https://www.excelguru.ca/blog/2016/08/23/fix-excel-formulas-dont-update-in-power-query-tables/

Hope this helps,
Mike

 

[collin8579]

Hi Collin,

Make sure that your columns FCST RE 103 and ACT RE 103 were changed type to date in your Get Data & Transform.

Try this link to debug the formulas not filling down correctly.
https://www.excelguru.ca/blog/2016/08/23/fix-excel-formulas-dont-update-in-power-query-tables/

Hope this helps,
Mike

Mike,
Below is what I pulled out of the advanced editor. (I removed the extensive items I removed and changed to date to show only the ones from above)
Its about 12k Records, and it only seems to alter the items "Extra" from the last query run... IE
IF I have 100 records on first update, I have all dates at proper format
then bring in 110 when I update the query
it is only the 10 that will have the weird date formats

It is almost like the "Table" that is represented doesn't expand to encompass new records,, is that a thing?

>>>

let
Source = Table.Combine({#"Florida Pace Data", #"GA SC Pace Data", #"NTX Pace Data", #"STX Pace data", #"Gulf States Pace Data", #"TN KY Pace Data", #"AR OK Pace Data"}),
#"Removed Columns" = Table.RemoveColumns(Source,{ALLLLL Of the removed columns}),
#"Changed Type" = Table.TransformColumnTypes(#"Removed Columns",(ALLL the other columns left changing date), {"FCST RE103-Lease Draft Received From Tower Owner", type date}, {"ACT RE103-Lease Draft Received From Tower Owner", type date}})
in
#"Changed Type"

Thank you,
Collin

 

[collin8579]

I confirmed the table should propogate formulas per the above mention.

So one thing I noticed today when updating the query, when i click on the table created by the query it doesn't extend to the new records. The Table bounds itself.
Is there a part of query that resets the table bounds based on the new records?
Thank you,
Collin