Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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"
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
100 | |
54 | |
21 | |
12 | |
11 |