Forum Discussion
Transforming Columns with specific values
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
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?
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
Try this:
= Table.ReplaceValue(Source,each [Date],Text.From(Date.From(Date.StartOfMonth(DateTime.LocalNow()))),Replacer.ReplaceValue,{"Date"})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
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 _ kalyjIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
