Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Anonymous
Not applicable

Group column values by date

Hello to all,

Here is my data table :

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

image.png


Thank you in advance,

Joël

2 ACCEPTED SOLUTIONS
smpa01
Super User
Super User

 

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"

 

 

dewsq.PNG

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

View solution in original post

Anonymous
Not applicable

 

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"

 

View solution in original post

3 REPLIES 3
Anonymous
Not applicable

 

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"

 

Anonymous
Not applicable

Thank you very much @smpa01  and see you soon

smpa01
Super User
Super User

 

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"

 

 

dewsq.PNG

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

Helpful resources

Announcements
August Power BI Update Carousel

Power BI Monthly Update - August 2025

Check out the August 2025 Power BI update to learn about new features.

August 2025 community update carousel

Fabric Community Update - August 2025

Find out what's new and trending in the Fabric community.

Top Solution Authors