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

collin8579

New Member
Joined
Oct 31, 2017
Messages
20
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.
A9WOrIf.png

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
 

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,517
Messages
6,114,089
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top