Forum Discussion
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...
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
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.
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