Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Transforming Columns with specific values

Hi all, I hope someone can provide a suggestion/solution to this task. I have 2 columns I  need to perform transformations on .The first one is in this format: What I would like to do is to t...
Need Help
  • ronrsnfld's avatar
    ronrsnfld
    4 years ago

    If I understand correctly, you can try the following:

    • Use the List.Generate function to generate the basis for your new columns
    • After creating the table, with the proper dates and prices in the columns,
      • Transform the Date column into text in the format you specify
      • Set the data types for the Date and Price column appropriately.

     

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("NcjBCQAgDAPAXfqW1kSFOIu4/xoW0XveWgZGQ7ASVmyqu2C75L9mNupornGfQf0X6T17Hw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Price = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Price", type number}}),

   newCols=List.Generate(
       ()=>[d=Date.StartOfMonth(DateTime.LocalNow()), p=List.Last(#"Changed Type"[Price]), idx=0],
       each [idx]<Table.RowCount(#"Changed Type"),
       each [d=Date.AddMonths([d],1), p=List.Last(#"Changed Type"[Price]), idx=[idx]+1],
       each {[d],[p]}),

    newTbl = Table.FromRecords(
        List.Transform(newCols, each Record.FromList(_,{"Date","Price"}))),

    #"Date to Text" = Table.TransformColumns(newTbl,
        {"Date", each DateTime.ToText(_,"MM/dd/yyyy")}),
        
    #"Changed Type1" = Table.TransformColumnTypes(#"Date to Text", {
        {"Date", type text},{"Price", Currency.Type}
    })
in
    #"Changed Type1"

     

     

     

    Original Table

     

    Results

     

     

ronrsnfld's avatar
ronrsnfld
Super User
4 years ago

Your explanation is not clear to me.

You write: 

"The second and third dates in similar way?"

Since similar is not the same as identical, what, exactly do you want?  Do you want ALL of the dates in the column to have today's date?  Only the first three?  Something else for rows other than the first?

 

You also write:

"I would like the first two rows to have the value of the third row"

But what if there are more than three rows?

 

What you ask for can be done, without adding an extra column (or an index column), but could you please clarify exactly what you want, and also indicate what happens if there are more rows than the three you show.

 

Anonymous's avatar
Anonymous
Not applicable
4 years ago

what I mean is that in the Date column ,each value should be transformed in different way,for example the first one with Datetime.LocalNow() function ,the second  with Date.AddMonths( Datetime.LocalNow(),1) , third value - Date.AddMonths( Datetime.LocalNow(),2) .Syntax might not be correct.

For the Price column,each row should have the value of the last row.

  • ronrsnfld's avatar
    ronrsnfld
    Super User
    4 years ago

    If I understand correctly, you can try the following:

    • Use the List.Generate function to generate the basis for your new columns
    • After creating the table, with the proper dates and prices in the columns,
      • Transform the Date column into text in the format you specify
      • Set the data types for the Date and Price column appropriately.

     

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("NcjBCQAgDAPAXfqW1kSFOIu4/xoW0XveWgZGQ7ASVmyqu2C75L9mNupornGfQf0X6T17Hw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Price = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Price", type number}}),

   newCols=List.Generate(
       ()=>[d=Date.StartOfMonth(DateTime.LocalNow()), p=List.Last(#"Changed Type"[Price]), idx=0],
       each [idx]<Table.RowCount(#"Changed Type"),
       each [d=Date.AddMonths([d],1), p=List.Last(#"Changed Type"[Price]), idx=[idx]+1],
       each {[d],[p]}),

    newTbl = Table.FromRecords(
        List.Transform(newCols, each Record.FromList(_,{"Date","Price"}))),

    #"Date to Text" = Table.TransformColumns(newTbl,
        {"Date", each DateTime.ToText(_,"MM/dd/yyyy")}),
        
    #"Changed Type1" = Table.TransformColumnTypes(#"Date to Text", {
        {"Date", type text},{"Price", Currency.Type}
    })
in
    #"Changed Type1"

     

     

     

    Original Table

     

    Results