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 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!
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
10 Replies
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?
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?
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.
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
