Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
Hello wise ones. I have a dataset that contains child class years and I want them to be in order, oldest to greatest. Ex. in the first row it should be 1982, 1992, 1994. Any idea of a way to do that in Power Query?
Solved! Go to Solution.
Hi, @danielrsnyder ;
You also could try it.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMrS0NFHSAVIWRmDKEkTllebkKMXqgGQtLBDCRgYGhmiyUE0GECMMwIoMzZViYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Child Class of" = _t, #"Child Class of2" = _t, #"Child Class of3" = _t, #"Child Class of4" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Child Class of", Int64.Type}, {"Child Class of2", Int64.Type}, {"Child Class of3", Int64.Type}, {"Child Class of4", Int64.Type}}),
#"Added Index" = Table.AddIndexColumn(#"Changed Type", "Group", 1, 1, Int64.Type),
#"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Added Index", {"Group"}, "Attribute", "Value"),
#"Grouped Rows" = Table.Group(#"Unpivoted Columns", {"Group"}, {{"Sort", each Table.AddIndexColumn( Table.Sort(_,{{"Group", Order.Ascending}, {"Value", Order.Ascending}}), "sort",1,1), type table}}),
#"Expanded Sort" = Table.ExpandTableColumn(#"Grouped Rows", "Sort", {"Attribute", "Value", "sort"}, {"Attribute", "Value", "sort.1"}),
#"Removed Columns" = Table.RemoveColumns(#"Expanded Sort",{"Attribute"}),
#"Added Custom" = Table.AddColumn(#"Removed Columns", "Custom", each "Child Class of"& Text.From([sort.1])),
#"Removed Columns1" = Table.RemoveColumns(#"Added Custom",{"sort.1"}),
#"Pivoted Column" = Table.Pivot(#"Removed Columns1", List.Distinct(#"Removed Columns1"[Custom]), "Custom", "Value", List.Sum),
#"Removed Columns2" = Table.RemoveColumns(#"Pivoted Column",{"Group"})
in
#"Removed Columns2"
Thr orginal table.
and the final show:
Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi, @danielrsnyder ;
You also could try it.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMrS0NFHSAVIWRmDKEkTllebkKMXqgGQtLBDCRgYGhmiyUE0GECMMwIoMzZViYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Child Class of" = _t, #"Child Class of2" = _t, #"Child Class of3" = _t, #"Child Class of4" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Child Class of", Int64.Type}, {"Child Class of2", Int64.Type}, {"Child Class of3", Int64.Type}, {"Child Class of4", Int64.Type}}),
#"Added Index" = Table.AddIndexColumn(#"Changed Type", "Group", 1, 1, Int64.Type),
#"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Added Index", {"Group"}, "Attribute", "Value"),
#"Grouped Rows" = Table.Group(#"Unpivoted Columns", {"Group"}, {{"Sort", each Table.AddIndexColumn( Table.Sort(_,{{"Group", Order.Ascending}, {"Value", Order.Ascending}}), "sort",1,1), type table}}),
#"Expanded Sort" = Table.ExpandTableColumn(#"Grouped Rows", "Sort", {"Attribute", "Value", "sort"}, {"Attribute", "Value", "sort.1"}),
#"Removed Columns" = Table.RemoveColumns(#"Expanded Sort",{"Attribute"}),
#"Added Custom" = Table.AddColumn(#"Removed Columns", "Custom", each "Child Class of"& Text.From([sort.1])),
#"Removed Columns1" = Table.RemoveColumns(#"Added Custom",{"sort.1"}),
#"Pivoted Column" = Table.Pivot(#"Removed Columns1", List.Distinct(#"Removed Columns1"[Custom]), "Custom", "Value", List.Sum),
#"Removed Columns2" = Table.RemoveColumns(#"Pivoted Column",{"Group"})
in
#"Removed Columns2"
Thr orginal table.
and the final show:
Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@v-yalanwu-msft Thanks for this, it worked great for my needs and got me to the solution I needed.
One of the problems with an Ascending Sort is that it will move the nulls to the start. To deal with that issue, I sort each row ascending; Remove the nulls; then pad the end of the list with nulls so each row will have the correct number of entries:
let
Source = Excel.CurrentWorkbook(){[Name="Table15"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Child Class of", Int64.Type}, {"Child Class of2", Int64.Type}, {"Child Class of3", Int64.Type}, {"Child Class of4", Int64.Type}}),
ColCount=Table.ColumnCount(#"Changed Type"),
colHdrs = Table.ColumnNames(#"Changed Type"),
//With Ascending sort, nulls will be at the start
// Note the padding to move them to the end
#"Sorted Rows" =
Table.FromRows(
List.Accumulate({0..Table.RowCount(#"Changed Type")-1}, {}, (state, current)=> state &
{List.Split(
List.Sort(
List.RemoveNulls(
Record.FieldValues(#"Changed Type"{current})), Order.Ascending) &
List.Repeat({null}, ColCount),
ColCount){0}}),
colHdrs),
Typed = Table.TransformColumnTypes(#"Sorted Rows", List.Transform(colHdrs, each {_, Int64.Type}))
in
Typed
This points to a bad source data format design. What are you actually trying to accomplish?
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
If you love stickers, then you will definitely want to check out our Community Sticker Challenge!
User | Count |
---|---|
18 | |
10 | |
9 | |
9 | |
8 |
User | Count |
---|---|
17 | |
16 | |
14 | |
12 | |
12 |