Power Query Transform & Table Formulas not propagating to new records brought in
Results 1 to 4 of 4

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

  1. #1
    New Member
    Join Date
    Oct 2017
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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

  2. #2
    New Member
    Join Date
    Jun 2019
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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/0...-query-tables/

    Hope this helps,
    Mike

  3. #3
    New Member
    Join Date
    Oct 2017
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    Quote Originally Posted by cyborgski View Post
    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/0...-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

  4. #4
    New Member
    Join Date
    Oct 2017
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •