Forum Discussion
Solved
Create a Master Date column
Hi!
So my data currently looks like this:
| Project # | Booked Date | Billed Date | $ Amount |
| 1 | 11/1/2021 | $10 | |
| 2 | 11/2/2021 | 12/4/2021 | $13 |
| 3 | 11/5/2021 | 12/1/2021 | $5 |
This is how i want it to look (basicially just one "date" column instead of 2 & new columns for bookings and billings)
| Project # | Date | Bookings $ | Billings $ |
| 1 | 11/1/2021 | $10 | |
| 2 | 11/2/2021 | $13 | |
| 2 | 12/4/2021 | $13 | |
| 3 | 11/5/2021 | $5 | |
| 3 | 12/1/2021 | $5 |
Any ideas on how I can create this?
Thanks!!
1. Unpivot Booked Date, Billed Date
2. replace - In New Attribute column , Replace Date -> $
3. Pivot -> Amount based on Attribute
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTI01DfUNzIwArEVgFjF0EApVidayQgiZwSTMzTSN4GxVQyNwWqMIWpMkdTAzVIxVYqNBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Project #" = _t, #"Booked Date" = _t, #" Billed Date" = _t, #"$ Amount" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Project #", Int64.Type}, {"Booked Date", type date}, {" Billed Date", type date}, {"$ Amount", Currency.Type}}), #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Project #", "$ Amount"}, "Attribute", "Value"), #"Replaced Value" = Table.ReplaceValue(#"Unpivoted Columns","Date","$",Replacer.ReplaceText,{"Attribute"}), #"Pivoted Column" = Table.Pivot(#"Replaced Value", List.Distinct(#"Replaced Value"[Attribute]), "Attribute", "$ Amount", List.Sum) in #"Pivoted Column"
1 Reply
1. Unpivot Booked Date, Billed Date
2. replace - In New Attribute column , Replace Date -> $
3. Pivot -> Amount based on Attribute
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTI01DfUNzIwArEVgFjF0EApVidayQgiZwSTMzTSN4GxVQyNwWqMIWpMkdTAzVIxVYqNBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Project #" = _t, #"Booked Date" = _t, #" Billed Date" = _t, #"$ Amount" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Project #", Int64.Type}, {"Booked Date", type date}, {" Billed Date", type date}, {"$ Amount", Currency.Type}}), #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Project #", "$ Amount"}, "Attribute", "Value"), #"Replaced Value" = Table.ReplaceValue(#"Unpivoted Columns","Date","$",Replacer.ReplaceText,{"Attribute"}), #"Pivoted Column" = Table.Pivot(#"Replaced Value", List.Distinct(#"Replaced Value"[Attribute]), "Attribute", "$ Amount", List.Sum) in #"Pivoted Column"