Create a Master Date column

[PBINewbie920]

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!!

[amitchandak · Accepted solution]

@PBINewbie920 ,

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"

https://radacad.com/pivot-and-unpivot-with-power-bi