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

     

     

PC2790's avatar
PC2790
Community Champion
4 years ago

Try this:

 

= Table.ReplaceValue(Source,each [Date],Text.From(Date.From(Date.StartOfMonth(DateTime.LocalNow()))),Replacer.ReplaceValue,{"Date"})
Anonymous's avatar
Anonymous
Not applicable
4 years ago

That works but this replaces all the values with the column  and what I would like to do is to access somehow each value separately by their index and apply different functions on each of them 

  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    Community Support
    4 years ago

    Hi Anonymous ,

    As you say, to determine the index, there should be at least an index column, and here's my solution.

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("RcixDQAhDAPAXVyjBBt4hVlQ9l8DBMXrulsLqqJT3oiC+XULIst9OV+zjmYx/z/ifEjWBzI3", 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}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),
    #"New Date" =Table.ReplaceValue(#"Added Index",each [Date],each if [Index]=0 then Text.From(Date.From(Date.StartOfMonth(DateTime.LocalNow())))else [Date],Replacer.ReplaceValue,{"Date"}),
    #"New Price" = Table.ReplaceValue(#"New Date",each [Price],each if [Index]=0 or [Index]=1 then #"New Date"[Price]{2} else[Price],Replacer.ReplaceValue,{"Price"})
in
    #"New Price"

    Get the result.

    I attach my sample below for reference.

     

    Best Regards,
    Community Support Team _ kalyj

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.