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

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

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,134
Messages
5,484,935
Members
407,475
Latest member
Dix_Fix

This Week's Hot Topics

  • Finding issue in If elseif else with For each Loop
    Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
  • MsgBox Error
    Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
  • CELL FORMAT - IF CONDITION
    My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
  • Show numbers nearly the same
    Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...
  • Please i need your help to create formula
    I need a formula in cell B8 to do this >>if b1=1 then multiply ( cell b8) by 10% ,if b1=2 multiply by 20%,if=3 multiply by 30%. Thank you in...
  • Got error while adding column and filter
    Got error while adding column and filter In column Z has some like "Success" and "Error". I want to add column in AA if the Z cell value is...
Top