cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper IV
Helper IV

Amending existing table to have calendar date added as extra column

Hello ,

 

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

IDAmountDate ADate B
2987046551610-03-202020-02-2020
29870526206.416-04-202001-04-2020
29870627005.412-05-202023-04-2020
29870767513.501-04-202017-03-2020

 

O/P Table :

DateIDAmountDate ADate B
01-01-20202987046551610-03-202020-02-2020
01-01-202029870526206.416-04-202001-04-2020
01-01-202029870627005.412-05-202023-04-2020
01-01-202029870767513.501-04-202017-03-2020
02-01-20202987046551610-03-202020-02-2020
02-01-202029870526206.416-04-202001-04-2020
02-01-202029870627005.412-05-202023-04-2020
02-01-202029870767513.501-04-202017-03-2020
03-01-20202987046551610-03-202020-02-2020
03-01-202029870526206.416-04-202001-04-2020
03-01-202029870627005.412-05-202023-04-2020
03-01-202029870767513.501-04-202017-03-2020

 

1 REPLY 1
Highlighted
Super User III
Super User III

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"

Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog:

Power BI em Português





Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors