Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

Newbie challenge

Hi,

 

As a PQ newbie I can't solve this simple transformation. The data structure is very simple and obvious for a human. However, PQ can't seem to be able to figure it out with "column from example". I am looking for a solution with minimal manual intervention. Here is the actual dataset and the desired result (bear in mind that there could be 100s of different names and various number of dates for each name) :

 

 

 

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Simple for a human isn't necessarily simple for a machine...

     

    You can add a column like


    = Table.AddColumn(PriorStepOrTableName, "Dates", each if Text.Contains([Date], "-") then null else [Date], type text)

     

    Now you can just use Fill Down on the new column.

     

    --Nate

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Simple for a human isn't necessarily simple for a machine...

     

    You can add a column like


    = Table.AddColumn(PriorStepOrTableName, "Dates", each if Text.Contains([Date], "-") then null else [Date], type text)

     

    Now you can just use Fill Down on the new column.

     

    --Nate

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      Excellent! Thank you Nate.

       

      There is a little extra step, that is to replace "Error" by "null" before fill down.

       

       A little less newbie now 🙂

      • mussaenda's avatar
        mussaenda
        Community Champion
        2 years ago

        right click on the red line above and select replace errors and then type null