Converting a list of values into a value per day, from a start date

[Anonymous]

Hi,

I'm hoping for a gem of advice....I'm not super experienced yet and not sure whether to attack this in the query using M, or via DAX in the table.

I have two relevant columns:

• Start date - e.g. "11/20/2018"
• MinutesPerDayList - eg "240,120,0,0,60,0,0,0"

Each of the values is the number of minutes per day, where 240 relates to the first day (20th November), 120 is for the 21st November, 0 for 22nd November and so on.

I don't really care about the 0 value days and plan to discard them later but I need to know that there were 60 minutes for the 24th November.  

I can convert the list of minutes into rows, but they all have the same start date of the 20th November.  Ideally I want to end up with a row per date with the correct number of minutes.  

Any ideas would be hugely appreciated.

Thanks

Regards, David.

[Zubair_Muhammad · Accepted solution]

HI @Anonymous

Try this

Please see the attached file here as well

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjJQ8MsvUzAyMLRQ0lEyMjHQMTQy0AFBMwhloBQbCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Start date" = _t, MinutesPerDayList = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Start date", type datetime}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "MinutesPerDayList", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"MinutesPerDayList.1", "MinutesPerDayList.2", "MinutesPerDayList.3", "MinutesPerDayList.4", "MinutesPerDayList.5", "MinutesPerDayList.6", "MinutesPerDayList.7", "MinutesPerDayList.8"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"MinutesPerDayList.1", Int64.Type}, {"MinutesPerDayList.2", Int64.Type}, {"MinutesPerDayList.3", Int64.Type}, {"MinutesPerDayList.4", Int64.Type}, {"MinutesPerDayList.5", Int64.Type}, {"MinutesPerDayList.6", Int64.Type}, {"MinutesPerDayList.7", Int64.Type}, {"MinutesPerDayList.8", Int64.Type}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type1", {"Start date"}, "Attribute", "Value"),
    #"Added Index" = Table.AddIndexColumn(#"Unpivoted Columns", "Index", 0, 1),
    #"Added Custom" = Table.AddColumn(#"Added Index", "Date", each Date.AddDays([Start date], [Index])),
    #"Reordered Columns" = Table.ReorderColumns(#"Added Custom",{"Start date", "Index", "Attribute", "Date", "Value"}),
    #"Removed Columns" = Table.RemoveColumns(#"Reordered Columns",{"Start date", "Index", "Attribute"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Value", "Minutes"}})
in
    #"Renamed Columns"

[Anonymous]

Hi Zubair. Thank you!!!  This is exactly the guidance I needed.  Thanks also for the sample PBIX file...it really helped understand the solution.

I only had one stumbling block in implementing it.  The thing is, with multiple records, the index number was no good to use because it kept incrementing rather than giving me an index number per original record (though if there is a way to do that I am interested).  From the second record onwards, the date calculations were wrong.  However, I realised that I could use the numbers from the Attribute column which was created by unpivoting.  I just split them out and subtracted one from each. 

This worked perfectly and has solved a big challenge for me.  1000 points for you 🙂

Cheers