Solved: Group column values by date

Anonymous · ‎01-22-2021

Hello to all,

Here is my data table :

I would like to be able to group my column values by their dates like this:

Thank you in advance,

Joël

smpa01 · ‎01-22-2021

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtQ31DcyMDJU0lECYSiK1YlWMkLIGKHKGCNkjFFlkExDMhDNNCQD0UwDIhOcpsEMxDQNZiCmaTAXgmRMiNcDtgqrHrC22FgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, M1 = _t, M2 = _t, M3 = _t, M4 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"M1", Int64.Type}, {"M2", Int64.Type}, {"M3", Int64.Type}, {"M4", Int64.Type}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1, Int64.Type),
    #"Reordered Columns" = Table.ReorderColumns(#"Added Index",{"Index", "Date", "M1", "M2", "M3", "M4"}),
    #"Sorted Rows" = Table.Sort(#"Reordered Columns",{{"Date", Order.Ascending}}),
    #"Removed Columns" = Table.RemoveColumns(#"Sorted Rows",{"Index"}),
    #"Grouped Rows" = Table.Group(#"Removed Columns", {"Date"}, {{"ad", each _, type table [Date=nullable date, M1=nullable number, M2=nullable number, M3=nullable number, M4=nullable number]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each let 
 #"Removed Columns1" = Table.RemoveColumns([ad],{"Date"}),
    Custom2 = Table.ColumnNames(#"Removed Columns1"),
    Custom1 = Table.ToColumns(#"Removed Columns1"),
    #"Converted to Table" = Table.FromList(Custom1, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Added Custom" = Table.AddColumn(#"Converted to Table", "Custom", each List.RemoveItems([Column1],{null})),
    Custom = #"Added Custom"[Custom],
    Custom3 = Table.FromColumns(Custom,Custom2)
    in Custom3),
    #"Removed Columns1" = Table.RemoveColumns(#"Added Custom",{"ad"}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Columns1", "Custom", {"M1", "M2", "M3", "M4"}, {"M1", "M2", "M3", "M4"})
in
    #"Expanded Custom"

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

Proud to be a Super User!

My custom visualization projects

Plotting Live Sound: Viz1

Beautiful News:Viz1, Viz2, Viz3

Visual Capitalist: Working Hrs

Others:Easing Graph, Animated Calendar

View solution in original post

Anonymous · ‎01-24-2021

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtQ31DcyMDJU0lECYSiK1YlWMkLIGKHKGCNkjFFlkExDMhDNNCQD0UwDIhOcpsEMxDQNZiCmaTAXgmRMiNcDtgqrHrC22FgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, M1 = _t, M2 = _t, M3 = _t, M4 = _t]),
    #"Sostituito valore" = Table.ReplaceValue(Source,"",null,Replacer.ReplaceValue,{"M1", "M2", "M3", "M4"}),
    
coalescerec=(recs)=>
let
vrecs=List.Zip(List.Transform(recs, each Record.FieldValues(_))),
coal=(lv)=> List.Accumulate(lv,null,(s,c)=>s??c),
rv=List.Transform(vrecs, each coal(_)),
rn=Record.FieldNames(recs{0})
in Record.FromList(rv,rn),

    #"Raggruppate righe" = Table.Group(#"Sostituito valore", {"Date"}, {{"all", each coalescerec(Table.ToRecords(_))}}),
    #"Tabella all espansa" = Table.ExpandRecordColumn(#"Raggruppate righe", "all", {"M1", "M2", "M3", "M4"}, {"M1", "M2", "M3", "M4"})
in
    #"Tabella all espansa"