Forum Discussion
kaye123
3 years agoFrequent Visitor
TopN Based on Characters
I am trying to get only the last 4 quarters or last 13 weeks that will be using for the Sparkline reference. My only reference of ranking is based on sorting it form highest to lowest. But since thi...
ERD
Community Champion
3 years agokaye123 , you can
- write an SQL query with needed data and load it into the Power Query
- you can create a column in Power Query with transformed value converted to a number (2020_01 vs 202001) and use this new column for sorting, etc..
For the second option taking into account your column is text:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMog3MFRQitWBckDsWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
#"Split Column by Delimiter" = Table.SplitColumn(Source, "Column1", Splitter.SplitTextByEachDelimiter({"_"}, QuoteStyle.Csv, false), {"Column1.1", "Column1.2"}),
#"Added Custom" = Table.AddColumn(#"Split Column by Delimiter", "Custom", each [Column1.1] & Text.End( "0" & [Column1.2], 3 )),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Column1.2", "Column1.1"}),
#"Changed Type" = Table.TransformColumnTypes(#"Removed Columns",{{"Custom", Int64.Type}})
in
#"Changed Type"