Transforming Columns with specific values

[Anonymous]

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 transform each of the three dates within this column without adding a new custom/conditional column.The first date should be trandformed using this: 

Text.From(Date.From(Date.StartOfMonth(DateTime.LocalNow())))

The second and third dates in similar way.How can I accomplish this?

The second column  within the same table is this:

I would like the first two rows to have the value of the third row again without adding new column.

I have seen there is Table.TransformColumns function,but its implementation gives me an error.

Thanks in advance for your help!

[ronrsnfld]

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]

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 · Accepted solution]

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

[Vijay_A_Verma]

Can you also tell us the expected output for these two cases? In second case, will 4th & 5th be replaced by 6th row and so on?

[Anonymous]

The expected output is overwritten values in both cases

[PC2790]

Do you want the next two columns in the two respective rows to be replaced by 1st value of the  column?

How does your data look like?

Do you have only three rows which are expected to have fixed values?

[Anonymous]

in the Price column,the last value should be used to replace the first two.

In the dates column,the following should happen:

*The first value should be replaced with : 

=Text.From(Date.From(Date.StartOfMonth(DateTime.LocalNow())))

which will return a value of type text ,

the next two values with similar code.

I would like this to happen without adding new columns,so directly overwriting the column values 

[PC2790]

Try this:

= Table.ReplaceValue(Source,each [Date],Text.From(Date.From(Date.StartOfMonth(DateTime.LocalNow()))),Replacer.ReplaceValue,{"Date"})

[Anonymous]

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]

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.