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

collin8579

New Member
Joined
Oct 31, 2017
Messages
19
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
 

Some videos you may like

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

collin8579

New Member
Joined
Oct 31, 2017
Messages
19
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

New Member
Joined
Oct 31, 2017
Messages
19
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
 

Watch MrExcel Video

Forum statistics

Threads
1,102,778
Messages
5,488,808
Members
407,658
Latest member
Arias610

This Week's Hot Topics

  • Timer in VBA - Stop, Start, Pause and Reset
    [CODE=vba][/CODE] Option Explicit Dim CmdStop As Boolean Dim Paused As Boolean Dim Start Dim TimerValue As Date Dim pausedTime As Date Sub...
  • how to updates multiple rows in muliselect listbox
    Hello everyone. I need help with below code. code is only chaning 1st row in mulitiselect list box. i know issue with code...
  • Delete Row from Table
    I am trying to delete a row from a table using VBA using a named range to find what I need to delete. My Range is finding the right cell. In the...
  • Assigning to a variable
    I have a for each block where I want to assign the value in column 5 of the found row to the variable Serv. [CODE=vba] For Each ws In...
  • Way to verify information
    Hi All, I don't know what to call this formula, and therefore can't search. I have a spreadsheet with information I want to reference...
  • Active Cell Address – Inactive Sheet
    How to use VBA to get the cell address of the active cell in an inactive worksheet and then place that cell address in a location on the current...
Top