Graph axis to appear as data appears in graph, not sorted alphabetically

[Anonymous]

Hello,

I am hoping that someone can help me figure out how to essentially prevent PowerBI from applying ANY sorting while creating a graph. It's automatically sorting the data on my axis alphabetically, and only giving me the options to sort alphabetically in ascending or descending order... I don't want any sorting applied whatsoever, but for it to appear on the axis in the order it appears in the table so that you can see how numbers were affected chronologically through the periods within a year, from year to year.

For example, the data in my data appears as P1 - 2019, P2- 2019, P3 - 2019, P1 -  2020, P2 - 2020, P3 - 2020, etc.  but is graphing as P1 - 2019, P1 - 2020, P2 - 2019, P2 - 2020, etc.

[Anonymous]

That gives me the option to create a custom column with a formula, but this isn't a calculated set of data. I'm not sure how I would make that work.

[smpa01]

@Anonymous 

you need to create a calculated column for

the data in my data appears as P1 - 2019, P2- 2019, P3 - 2019, P1 -  2020, P2 - 2020, P3 - 2020, etc.

so that it appears as

2019-01, 2019-02, 2019-03

and use that column in your viz axis

[Anonymous]

I understand that, but am very new to PowerBI. I'm looking at equation documentation but do not know how to quickly/cleanly create that without having to write a replacement equation for every single cell. If you have any resources, it would be much appreciated.

[smpa01 · Accepted solution]

@Anonymousyou can easily do that transformation in pqwry

let
    Source = {1..12},
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Added Prefix" = Table.TransformColumns(#"Converted to Table", {{"Column1", each "P" & Text.From(_, "en-US"), type text}}),
    #"Added Suffix" = Table.TransformColumns(#"Added Prefix", {{"Column1", each _ & "-2019", type text}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Added Suffix", "Column1", Splitter.SplitTextByDelimiter("-", QuoteStyle.Csv), {"Column1.1", "Column1.2"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Column1.1", type text}, {"Column1.2", Int64.Type}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type","P","",Replacer.ReplaceText,{"Column1.1"}),
    #"Added Custom" = Table.AddColumn(#"Replaced Value", "Custom", each if Text.Length([Column1.1])<2 then Text.From([Column1.2])&"-"&Text.PadStart([Column1.1],2,"0") else Text.From([Column1.2])&"-"&[Column1.1])
in
    #"Added Custom"

[smpa01]

@Anonymous  you need to create a custom column for X axis as 2019-01, 2019-02, 2019-03 .....for power bi to show the axis in that order