Specific transformation of split column values


Scenario: 

In many cases, it involves splitting the value. If it is just a basic splitting of the value, it can be achieved by using Split function in Power query. However, in the following case, the user has different values in each cell with “|” as a separator and needs to remove the separator and sort the values in reverse order based on the date after the separator is removed.

Table Used: 

The following is base table:

Vlianlmsft_0-1648631994359.png

 

Expect output: (The first date value is the date of the current day. (2021/03/22 in the figure below)

Vlianlmsft_1-1648631994362.png

 

Solution,

Step 1,

Create a date table to setup the “Endtime” in a dynamic way so that we can take today’s date as the end date.  To achieve this, create a Blank Query and use”= {Number.From(#date(2022,1,1)). Number.From(DateTime.Date(DateTime.LocalNow()))}”, then convert the Query to a table and change the Number to a Date.

 

 

Vlianlmsft_2-1648631994366.png

 

 

Vlianlmsft_3-1648631994368.png

 

Vlianlmsft_4-1648631994370.png

 

In the Advanced editor, it has the following full code:

 

let

    Source = {Number.From(#date(2022,1,1))..Number.From(DateTime.Date(DateTime.LocalNow()))},

    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),

    #"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"Column1", type date}}),

    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"Column1", Order.Descending}}),

    #"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Index", 1, 1, Int64.Type)

in

    #"Added Index"    #"Added Custom1" = Table.AddColumn(#"Changed Type1", "Custom", each {Number.From([Date])..Number.From([Endtime])}),

    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom1", "Custom"),

    #"Removed Columns" = Table.RemoveColumns(#"Expanded Custom",{"Date", "Endtime"}),

    #"Changed Type2" = Table.TransformColumnTypes(#"Removed Columns",{{"Custom", type date}}),

    #"Sorted Rows" = Table.Sort(#"Changed Type2",{{"Custom", Order.Descending}}),

    #"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Index", 1, 1, Int64.Type)

in

#"Added Index"

 

 

Step 2,

 Perform some transformation operations on the base table, and the specific steps can be found in the code. First, take the data in the row, get it according to the split symbol “|”, reverse it, then remove the “|” and present it as a column.

Vlianlmsft_5-1648631994371.png

 

Vlianlmsft_6-1648631994374.png

 

Step 3,

Group sort again according to the current order.  Grouping based on the initial value since we don’t have a field to fix the current order. Therefore, we have to create an index and group sort with the data of the index value. And remember to delete useless columns.

Vlianlmsft_7-1648631994377.png

 

 

Vlianlmsft_8-1648631994378.png

 

Vlianlmsft_9-1648631994379.png

 

Vlianlmsft_10-1648631994381.png

 

Step 4,

Use index-based sorting column associated with the indexes in the date table, merge them into the original table, remove the useless fields and pivot the table:

Vlianlmsft_11-1648631994383.png

 

Vlianlmsft_12-1648631994386.png

 

Vlianlmsft_13-1648631994390.png

 

Vlianlmsft_14-1648631994392.png

 

The full code could refer the Advanced editor:

 

let

    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WqjHUM64x0jOtMdazqDHRM6sx1TNQitXBLlFjpmdYY65nSFBBjaWeqVJsLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [value = _t]),

    #"Added Custom" = Table.AddColumn(Source, "Custom", each List.Reverse(Text.Split([value], "|"))),

    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"),

    #"Added Index" = Table.AddIndexColumn(#"Expanded Custom", "Index", 1, 1, Int64.Type),

    #"Grouped Rows" = Table.Group(#"Added Index", {"value"}, {{"Count", each _, type table [value=nullable text, Custom=text, Index=number]}}),

    #"Added Index1" = Table.AddColumn(#"Grouped Rows", "newrank", each Table.AddIndexColumn([Count], "newrank", 1, 1)),

    #"Expanded newrank" = Table.ExpandTableColumn(#"Added Index1", "newrank", {"value", "Custom", "Index", "newrank"}, {"newrank.value", "newrank.Custom", "newrank.Index", "newrank.newrank"}),

    #"Removed Columns" = Table.RemoveColumns(#"Expanded newrank",{"newrank.value", "newrank.Index", "Count"}),

    #"Merged Queries" = Table.NestedJoin(#"Removed Columns", {"newrank.newrank"}, Query1, {"Index"}, "Datetable (2)", JoinKind.LeftOuter),

    #"Expanded Datetable (2)1" = Table.ExpandTableColumn(#"Merged Queries", "Datetable (2)", {"Column1", "Index"}, {"Datetable (2).Column1", "Datetable (2).Index"}),

    #"Removed Columns1" = Table.RemoveColumns(#"Expanded Datetable (2)1",{"newrank.newrank", "Datetable (2).Index"}),

    #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Removed Columns1", {{"Datetable (2).Column1", type text}}, "zh-CN"), List.Distinct(Table.TransformColumnTypes(#"Removed Columns1", {{"Datetable (2).Column1", type text}}, "zh-CN")[#"Datetable (2).Column1"]), "Datetable (2).Column1", "newrank.Custom")

in

    #"Pivoted Column"

 

 

Result:

Vlianlmsft_15-1648631994394.png

Hope this article helps everyone with similar questions here. 

 

 

 

Author: Lucien Wang

Reviewer: Kerry Wang & Ula Huang

Specific transformation of split column values.pbix



Source link

Be the first to comment

Leave a Reply

Your email address will not be published.


*