Forum Discussion
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 loaded every month dynamically.
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.
2 Replies
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
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.
