Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

Columns with text and date - sort for visualization

I have following column in table which is text data type. I need to sort this table in chronological order for date and word in certian orders. Also it need to apply for all fuutre date which will be...
Need Help
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi Anonymous 

     

    AUDISU Thank you very much for your prompt reply.

     

    Here are some other thoughts I'd like to share.

     

    I noticed that your screenshot uses the SUMMARIZE and UNION functions to create a new table.


    In fact, when you create a visual with that data, power bi can help you sort it automatically. Or you can sort manually.

     

     

    You can click on the field to select ascending or descending order.

     

     

     

     

    Regards,

    Nono Chen

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

     

AUDISU's avatar
AUDISU
Icon for Resolver III rankResolver III
2 years ago

Hi Anonymous ,
Create a table in power query using following M code.

Then merge with your b table.


let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjDUNzDSNzIwMlGKjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [StartDate = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"StartDate", type date}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "EndDate", each Date.EndOfMonth( Date.From( DateTime.LocalNow()))),
#"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"EndDate", type date}}),
#"Added Custom1" = Table.AddColumn(#"Changed Type1", "Date", each {Number.From([StartDate])..Number.From([EndDate])}),
#"Expanded Date" = Table.ExpandListColumn(#"Added Custom1", "Date"),
#"Changed Type2" = Table.TransformColumnTypes(#"Expanded Date",{{"Date", type date}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type2",{"StartDate", "EndDate"}),
#"Changed Type3" = Table.TransformColumnTypes(#"Removed Columns",{{"Date", type text}})
in
#"Changed Type3"

Thanks