Forum Discussion

vineshparekh's avatar
vineshparekh
Helper I
3 years ago

Sort Columns Month wise

Hello,

 

I have few columns and I want to sort columns based on the month & Year. For example, 0723, 0823, 1023, 0124 etc..

 

My data is connected to a folder so everytime a new PDF is added with MMYY, the data will add a new column for that month.

 

For example, I have columns below.

Name, Category, Serial Number, then months 0723, 0823, 1023, 0124, Total

So I want to keep all the columns as it and the month columns should be sorted as the earliest month should appear first. As you see in the image below, it is showing 0723 before 0423

 

Need Help

1 Reply

  • Vijay_A_Verma's avatar
    Vijay_A_Verma
    Most Valuable Professional
    3 years ago

    Insert this step where Source should be replaced with your previous step name

    = [a = Table.ColumnNames(Source), i1 = List.PositionOf(a, "Serial Num"), i2 = List.PositionOf(a, "Total"), l1 = List.Range(a, 0, i1 + 1), l2 = List.Range(a, i1 + 1, i2 - i1 - 1), l3 = List.Range(a, i2, List.Count(a) - i2), result = Table.ReorderColumns(Source, l1 & List.Sort(l2) & l3)][result]

    For testing of above step, you can use this complet set of code

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSlTSUXIEYmMgNjICEqYmQAKEDUE8M1OQnIkpWFVsLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, Category = _t, #"Serial Num" = _t, #"0723" = _t, #"0423" = _t, #"0923" = _t, #"0924" = _t, #"0925" = _t, Total = _t, abc = _t]),
    Custom1 = [a = Table.ColumnNames(Source), i1 = List.PositionOf(a, "Serial Num"), i2 = List.PositionOf(a, "Total"), l1 = List.Range(a, 0, i1 + 1), l2 = List.Range(a, i1 + 1, i2 - i1 - 1), l3 = List.Range(a, i2, List.Count(a) - i2), result = Table.ReorderColumns(Source, l1 & List.Sort(l2) & l3)][result]
in
    Custom1