Amending existing table to have calendar date added as extra column

[kkalyanrr]

Hello ,

I'm trying to achieve the below result with my dataset, can you please help..

ID	Amount	Date A	Date B
298704	65516	10-03-2020	20-02-2020
298705	26206.4	16-04-2020	01-04-2020
298706	27005.4	12-05-2020	23-04-2020
298707	67513.5	01-04-2020	17-03-2020

O/P Table :

Date	ID	Amount	Date A	Date B
01-01-2020	298704	65516	10-03-2020	20-02-2020
01-01-2020	298705	26206.4	16-04-2020	01-04-2020
01-01-2020	298706	27005.4	12-05-2020	23-04-2020
01-01-2020	298707	67513.5	01-04-2020	17-03-2020
02-01-2020	298704	65516	10-03-2020	20-02-2020
02-01-2020	298705	26206.4	16-04-2020	01-04-2020
02-01-2020	298706	27005.4	12-05-2020	23-04-2020
02-01-2020	298707	67513.5	01-04-2020	17-03-2020
03-01-2020	298704	65516	10-03-2020	20-02-2020
03-01-2020	298705	26206.4	16-04-2020	01-04-2020
03-01-2020	298706	27005.4	12-05-2020	23-04-2020
03-01-2020	298707	67513.5	01-04-2020	17-03-2020

[MFelix]

Hi @kkalyanrr ,

If you want to delete the similar rows just delete the date row then select all columns and delete duplicates.

Check code below:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("nY9hCsMwCEbvkt+BfJqo21lK73+NxZUGimObhUCUL0/ztq2A2jwMRqmFnw/DmIWKkM6b0NBXOhs+mr1+IsULZWj1GaQN48z98fjG+jY2QA6WG2Tl/Qdr/mMT6lWuq+YgWwJvlm/bBjJhG9iEbWATtv22bSATtoFN2Ab2T9v9BQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, ID = _t, Amount = _t, #"Date A" = _t, #"Date B" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"ID", Int64.Type}, {"Amount", type number}, {"Date A", type date}, {"Date B", type date}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Date"}),
    #"Removed Duplicates" = Table.Distinct(#"Removed Columns")
in
    #"Removed Duplicates"