Forum Discussion

Narendra90's avatar
Narendra90
Frequent Visitor
3 years ago

want to transform data in 1 dates

Hi Team,

I have data (input) and required output here. Claim start date can be anything. Claim end date will be (mostly) +12 months of claim start date. Please guide me on this.

 

 

Need Help

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi Narendra90 ,

    I have created a simple sample, please refer to my pbix file to see if it helps you.

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMtY31DdU0oEyjfSNQRxDUwMDA6XYWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"claim start" = _t, #"claim end" = _t, #"claim fund amount" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"claim start", type date}, {"claim end", type date}, {"claim fund amount", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each List.Transform({Number.From([claim start])..Number.From([claim end])},each Date.From(_))),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"),
    #"Filtered Rows" = Table.SelectRows(#"Expanded Custom", each Date.Day([Custom]) = 1 ),
    #"Added Custom1" = Table.AddColumn(#"Filtered Rows", "Custom.1", each [claim fund amount] / Table.RowCount(#"Filtered Rows")),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom1",{"Custom", "Custom.1"})
in
    #"Removed Other Columns"

     

    How to Get Your Question Answered Quickly 

     

    If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

     

    Best Regards
    Community Support Team _ Rongtie

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.