The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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
Solved! Go to Solution.
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"
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"
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"
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"