Forum Discussion

Dubbie999's avatar
Dubbie999
New Member
2 years ago
Solved

Adding Column Removes Rows

Hi,

 

I have a problem that I have never encountered before.  When a add a column, power query removes the previous years row.  The column formula is "If [LoadType] = "FTL" then [PlanShipDate] else [SchedShipDate]"  Can someone tell me why this happens and how to stop if from happening.

 

Thanks

Need Help
  • Dubbie999's avatar
    Dubbie999
    2 years ago

    Thanks to all that responded.  I found the problem.  Someone changed the data table in the SQL database and the data was moved.

     

    Thanks again

3 Replies

Replies have been turned off for this discussion
  • Dubbie999's avatar
    Dubbie999
    New Member
    2 years ago

    Thanks to all that responded.  I found the problem.  Someone changed the data table in the SQL database and the data was moved.

     

    Thanks again

  • Legend_11's avatar
    Legend_11
    Resolver I
    2 years ago

    Could you please provide the sample data and also could you review the power query steps, 

     

    Please use the below formula maybe it is because of Nulls present in any of the columns,

    if [LoadType] = "FTL" and [PlanShipDate] <> null then [PlanShipDate]
    else if [SchedShipDate] <> null then [SchedShipDate]
    else null

  • VN999's avatar
    VN999
    Resolver I
    2 years ago

    Please check this below options :

     

    Check the Custom Column Formula:

    • Verify that your custom column formula is correct. The formula you mentioned should look like this in Power Query M language:      CustomColumn = if [LoadType] = "FTL" then [PlanShipDate] else [SchedShipDate]
    • Ensure No Unintended Filtering:Make sure there are no implicit filters applied in previous steps or within the formula itself. Sometimes, a filter applied in an earlier step can cause rows to be removed when adding a new column.

    • Verify Data Types:

      • Check the data types of LoadType, PlanShipDate, and SchedShipDate columns. Incorrect data types can cause unexpected behavior. Ensure LoadType is a text type and PlanShipDate and SchedShipDate are date types.