Forum Discussion

Patan7766's avatar
Patan7766
Regular Visitor
3 years ago

Montly data convert into daily

Date             value

Jan012022    50

Feb012022   60

Mar012022  40

The data available montly wise we need monthy data convert daily with respective month 

No of days respective month

Out put

Date            value

Jan012022  50/31

Jan022022  50/31

Jan032022  50/31

 

That month value generate continuous dates for respective month and divide no of days for that monthly value

 

 

Need Help

2 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    3 years ago

    OK. What have you tried and where are you stuck? Do you plan to do this in Power Query or in DAX?

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8krMMzA0MjAyUtJRMjVQitWJVnJLTYILmUGEfBOL4EImQKFYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Value = _t]),
    #"Replaced Value" = Table.ReplaceValue(Source,"01"," ",Replacer.ReplaceText,{"Date"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Replaced Value",{{"Date", type date}, {"Value", type number}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "End", each Date.EndOfMonth([Date]),type date),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Day", each {Int32.From([Date])..Int32.From([End])}),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Days", each List.Count([Day]),Int32.Type),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom2", "Day"),
    #"Added Custom3" = Table.AddColumn(#"Expanded Custom", "Day Value", each [Value]/[Days],type number),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom3",{"Day", "Day Value"})
in
    #"Removed Other Columns"

    How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done".

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    HI Patan7766,

    You can use following calculated column expression to extract the days from the current date string to calculate with the value column.

    Days =
VAR _date =
    DATEVALUE (
        LEFT ( [Date], 3 ) & "/"
            & LEFT ( RIGHT ( [Date], 6 ), 2 ) & "/"
            & RIGHT ( [Date], 4 )
    )
RETURN
    DAY ( DATE ( YEAR ( _date ), MONTH ( _date ) + 1, 1 ) - 1 )

    Regards

    Xiaoxin Sheng