Transforming Data by parsing, transposing and pivoting data from a text column

ibesmond · October 30, 2020, 10:42pm

Hello,

I’m trying to transform some data so I can run some analysis but I’m stuck. The goal is to run an analysis to see how many words are repeated between the customers. Such as “LIFTGATE”.
I figure if I can get each word from the notes column into rows, I can then count them to see which words are most frequent. As you will see, my parsing isn’t perfect

I have two columns. The first is a customer number and the second is a text field with random notes.
I want to parse the text field into rows.

I used

= Table.TransformColumns(#“Uppercased Text”,{{“Sticky Note Customer Master”, Splitter.SplitTextByAnyDelimiter({"|",","," “,”*","-"," “,”\n"} ), type text}}) to parse the data in the second column into a list.

Then I transposed the data and promoted column one to be headers.

I am now trying to expand the list in all of the columns, and I’m wondering if there is a way to do this for all columns.

Currently I have close to 1000 columns and don’t want to expand each one individually.

= Table.ExpandListColumn(#“Promoted Headers”, “10004”) for example. Can I replace “CUST1234” with something so that it expands every single column without having to write header names into the formula?

Once I have it all expanded I want to put it back into the format of two columns where in the first column it repeats customer numbers, and in the second column lists each row from the expanded rows. Haven’t got to that point if anyone has ideas about which tools to use to pivot the table back into a two column data set.

For an end result of this

Appreciate the advice in advanced. If there is an alternative, I am open to that also.

BrianJ · October 31, 2020, 1:10am

@ibesmond,

I built an example with a mix of delimiters (space, semicolon, slash and $). See if this general approach works for you:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("lZBBDsMgDAS/gqwekdKmTXvIaf0NlP9/I2ZtQnMIUg4IvGIGm1LkKVmAlFTTmoDYjqPXCq2J5wARVdlykZfz7Sam24a5G7idmuiCSNQoh7MFtlzyZtifVwy6+O+BipB8QrLe+A1DyC4X7Jj2YSyqw9DzpecR9CEbTNI6+JFkte0=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, TEXT = _t, RESULT = _t]),
    #"Removed Columns" = Table.RemoveColumns(Source,{"RESULT"}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Removed Columns", "TEXT", Splitter.SplitTextByAnyDelimiter({";", " ", "/", "$"})),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Split Column by Delimiter", {"ID"}, "Attribute", "Value"),
    #"Filtered Rows" = Table.SelectRows(#"Unpivoted Columns", each ([Value] <> "")),
    #"Removed Columns1" = Table.RemoveColumns(#"Filtered Rows",{"Attribute"}),
    #"Grouped Rows" = Table.Group(#"Removed Columns1", {"Value"}, {{"Count", each Table.RowCount(_), Int64.Type}})
in
    #"Grouped Rows"

I hope this is helpful. Full solution file posted below.

Brian

eDNA Forum- Count Delimited Text.pbix (22.9 KB)

ibesmond · October 31, 2020, 1:19am

@BrianJ.

Greatly appreciated! Thank you for the solution.

I did have one follow question. Do you know of a way to isolate dates, so they don’t get treated as a delimiter?

BrianJ · October 31, 2020, 6:05am

@ibesmond,

Definitely a higher degree of difficulty that one, but fun to work through it. I took the original dataset and seeded it with dates in the beginning, middle and end and before and after different delimiters:

The key is extracting the dates out before splitting by delimiter. This video shows how that can be done using Column by Example, as long as you have sufficient number of dates for the algorithm to figure out the pattern.

The next hurdle is that once you do split by delimiters, you’ll be left with a bunch of numeric date fragments. These can be identified and then subsequently filtered out by this code:

= Table.AddColumn(#"Removed Columns2", "Custom", each
     try Number.From([Value]) otherwise "Text")

Once you do that, duplicate the table. In the first version remove the date column, and in the second version remove the value column, rename date is value, remove duplicates and then append the two tables back together. Once that’s done you can group to get the final result the same way as before.

Here’s the M code and the steps:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("lZFdCoQwDISvUoqPQkzrLz4l15De/xo7TVp9WFZYqGjGzsdMe11ximMUCUE1nIEpZUoT70HEBJGzf/qsolVxXcTMqrGMV2QMcFf/NuDv7RH6m5U8Vd1pr+Zj0GfiY1067oE1RUFw0AgBjwOziU8UlZdELdWMKljJj0gN1nBzw90Ve0M6KDES7ttLV5iNsvygvJ+UF4RUCxpnNc7Q3DesU3Cta+3B3y17ks0JPFHmXjmW8gE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, TEXT = _t, RESULT = _t]),
    #"Removed Columns" = Table.RemoveColumns(Source,{"RESULT"}),
    #"Added Conditional Column" = Table.AddColumn(#"Removed Columns", "Date", each if [TEXT] = "AA  BB ; 1/23/2018 AABB ; AA; AABB ; BB ; ABAB; AAAA; AAAA" then #date(2018, 1, 23) else if [TEXT] = "3/2/2017$ AA ; BB ; AA/BB ; AA; AABB ; BB ; ABAB; AAAA; AAAA" then #date(2017, 3, 2) else if [TEXT] = "AA ; BBAA ; AABB ; 12/24/1965; AA; AA ; BB ; AB; AA; ABBB" then #date(1965, 12, 24) else if [TEXT] = "AAAA/BB ; AABA ; AA; AABB ; BB ; ABAB; AA; AAAA;4/20/2020" then #date(2020, 4, 20) else if [TEXT] = "AAAA; BB ; AABB ; AA;/9/21/1987 AABB ; BB ; ABAB; AAAA; AA" then #date(1987, 9, 21) else if [TEXT] = "AA$BB ; AA BB ; AABA; AABB  1/6/2021 BB ; ABAB; AA; AA" then #date(2021, 1, 6) else if [TEXT] = "AA$10/31/2020" then #date(2020, 10, 31) else null, type date),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Added Conditional Column", "TEXT", 
Splitter.SplitTextByAnyDelimiter({"/",";", " ","$"})),
    #"Reordered Columns" = Table.ReorderColumns(#"Split Column by Delimiter",{"ID", "Date", "TEXT.1", "TEXT.2", "TEXT.3", "TEXT.4", "TEXT.5", "TEXT.6", "TEXT.7", "TEXT.8", "TEXT.9", "TEXT.10", "TEXT.11", "TEXT.12", "TEXT.13", "TEXT.14", "TEXT.15", "TEXT.16", "TEXT.17", "TEXT.18", "TEXT.19", "TEXT.20", "TEXT.21", "TEXT.22", "TEXT.23", "TEXT.24"}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Reordered Columns", {"ID", "Date"}, "Attribute", "Value"),
    #"Removed Columns1" = Table.RemoveColumns(#"Unpivoted Columns",{"Attribute"}),
    #"Filtered Rows" = Table.SelectRows(#"Removed Columns1", each ([Value] <> "")),
    #"Duplicated Column" = Table.DuplicateColumn(#"Filtered Rows", "Value", "Value - Copy"),
    #"Removed Columns2" = Table.RemoveColumns(#"Duplicated Column",{"Value - Copy"}),
    #"Added Custom" = Table.AddColumn(#"Removed Columns2", "Custom", each
     try Number.From([Value]) otherwise "Text"),
    #"Filtered Rows2" = Table.SelectRows(#"Added Custom", each ([Custom] = "Text")),
    #"Removed Columns3" = Table.RemoveColumns(#"Filtered Rows2",{"Custom", "Date"}),
    #"Appended Query" = Table.Combine({#"Removed Columns3", #"Dates Only"}),
    #"Grouped Rows" = Table.Group(#"Appended Query", {"Value"}, {{"Count", each Table.RowCount(_), Int64.Type}})
in
    #"Grouped Rows"

I hope this is helpful. Full solution file below.

Brian

eDNA Forum- Count Delimited Text w Dates.pbix (25.6 KB)

Melissa · October 31, 2020, 11:38am

Great one @BrianJ
Love how you’ve maximized the use of the UI to built this solution.

For my own learning, I couldn’t resist making it way more complicated
Like Brian I think you should rely on Column from Examples to extract the dates.

CHANGES: updated delimiter list and changed Table.SplitColumn into Table.TransformColumns

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("lZFdCoQwDISvUoqPQkzrLz4l15De/xo7TVp9WFZYqGjGzsdMe11ximMUCUE1nIEpZUoT70HEBJGzf/qsolVxXcTMqrGMV2QMcFf/NuDv7RH6m5U8Vd1pr+Zj0GfiY1067oE1RUFw0AgBjwOziU8UlZdELdWMKljJj0gN1nBzw90Ve0M6KDES7ttLV5iNsvygvJ+UF4RUCxpnNc7Q3DesU3Cta+3B3y17ks0JPFHmXjmW8gE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, TEXT = _t, RESULT = _t]),
    CreateLists = Table.TransformColumns(Source[[TEXT]],{{"TEXT", Splitter.SplitTextByAnyDelimiter({"; ", "/", ";", "$", " "}, QuoteStyle.Csv, false), type text}}),
    DateOnly = Table.SelectRows(
        Table.AddColumn(Source, "Custom", each 
            if [TEXT] = "AA  BB ; 1/23/2018 AABB ; AA; AABB ; BB ; ABAB; AAAA; AAAA" then #date(2018, 1, 23) else 
            if [TEXT] = "3/2/2017$ AA ; BB ; AA/BB ; AA; AABB ; BB ; ABAB; AAAA; AAAA" then #date(2017, 3, 2) else 
            if [TEXT] = "AA ; BBAA ; AABB ; 12/24/1965; AA; AA ; BB ; AB; AA; ABBB" then #date(1965, 12, 24) else 
            if [TEXT] = "AAAA/BB ; AABA ; AA; AABB ; BB ; ABAB; AA; AAAA;4/20/2020" then #date(2020, 4, 20) else 
            if [TEXT] = "AAAA; BB ; AABB ; AA;/9/21/1987 AABB ; BB ; ABAB; AAAA; AA" then #date(1987, 9, 21) else 
            if [TEXT] = "AA$BB ; AA BB ; AABA; AABB  1/6/2021 BB ; ABAB; AA; AA" then #date(2021, 1, 6) else 
            if [TEXT] = "AA$10/31/2020" then #date(2020, 10, 31) 
            else null, type date)[[Custom]], each ([Custom] <> null)),
    TransformLists = Table.AddColumn( Table.FirstN( CreateLists, 1), "Custom", each 
        let
            NumToExcl = List.Transform( 
                List.RemoveNulls(
                    List.Transform( List.Combine(CreateLists[TEXT]), each try Number.From(_)*1 otherwise null) // Create a list with numbers
                ), each Text.From(_)  // Turn the numbers back into a type text
            )
        in 
        List.RemoveMatchingItems( 
            List.Combine(CreateLists[TEXT]),  
            List.Combine( {{null, ""}, NumToExcl}) // remove nulls, empty text strings and numbers as text
        )
    )[[Custom]], // Only keep the new column
    ExpandLists = Table.Combine({ Table.ExpandListColumn(TransformLists, "Custom"), DateOnly}),
    GroupData = Table.Group( ExpandLists, {"Custom"}, {{"Count", each Table.RowCount(_), Int64.Type}})
in
    GroupData

And this is the result.

Brian’s updated solution file: eDNA Forum- Count Delimited Text w Dates v2.pbix (36.9 KB)
I hope this is helpful

AntrikshSharma · October 31, 2020, 2:33pm

@Melissa & @BrianJ

ABBB be like:

BrianJ · October 31, 2020, 2:50pm

@AntrikshSharma,

And then ABBA is like, “Hey!, remember us?”…

@Melissa - thanks very much for the alternate solution. I definitely need to get more comfortable dealing with record types, which is why I think I sometimes have kludgy multi-table workarounds. It will be helpful working through your solution with the focus on that aspect.

Brian

AntrikshSharma · October 31, 2020, 2:51pm

This is what I got with THAT DAX:

Table = 
VAR Temp =
    ADDCOLUMNS (
        ADDCOLUMNS (
            VALUES ( Ibesmond[TEXT] ),
            "@Clean String",
                SUBSTITUTE ( SUBSTITUTE ( SUBSTITUTE ( SUBSTITUTE ( SUBSTITUTE ( SUBSTITUTE ( Ibesmond[TEXT], " ; ", "^" ), ";" , "^"), "$", "^" ), " ", "^" ), "^^", "^" ), "AA/BB", "AA^BB" )
        ),
        "@Word Count",
            1 + LEN ( [@Clean String] ) - LEN ( SUBSTITUTE ( [@Clean String], "^", "" ) )
    )
VAR UpperBound =
    MAXX ( Temp, [@Word Count] )
VAR GenerateSerialNumber =
    SELECTCOLUMNS ( GENERATESERIES ( 1, UpperBound, 1 ), "@Serial Number", [Value] )
VAR TempCrossJoin =
    CROSSJOIN ( Temp, GenerateSerialNumber )
VAR FilterSerialLessThanDelimiterCount =
    FILTER ( TempCrossJoin, [@Serial Number] <= [@Word Count] )
VAR SplitString =
    ADDCOLUMNS (
        FilterSerialLessThanDelimiterCount,
        "@Extracted Child", PATHITEM ( SUBSTITUTE ( [@Clean String], "^", "|" ), [@Serial Number] )
    )
VAR FinalTable =
    SELECTCOLUMNS (
        SplitString,
        "@Final String",
            VAR CurrentValue = [@Extracted Child]
            VAR Result =
                IF (
                    LEFT ( CurrentValue, 1 ) = "/",
                    MID ( CurrentValue, 2, LEN ( CurrentValue ) ),
                    CurrentValue
                )
            RETURN
                Result
    )
VAR Result =
    ADDCOLUMNS (
        SUMMARIZE ( FinalTable, [@Final String] ),
        "ValueCount",
            VAR CurrentValue = [@Final String]
            VAR Result =
                COUNTROWS ( FILTER ( FinalTable, [@Final String] = CurrentValue ) )
            RETURN
                Result
    )
RETURN
    Result

Look at the number of times I have used Final out of lack of creativity, reminds me of using Excel naming convention lazy people like me/I use “copy of copy of Project Final v3 (2).xlsx”

BrianJ · October 31, 2020, 3:09pm

@AntrikshSharma,

Hmmm…I thought you were just kidding about ABBB, but I see it is now missing. Trying to figure out what happened to it, since it seems to disappear in the Split Column by Delimiters step…

As always, that DAX is brilliant. I’ve never seen that nested SUBSTITUTE construct before, and I’m still working through how you extracted the dates. That one’s definitely getting it’s own Analyst Hub entry.

How long did it take you to write that beast?

Brian

AntrikshSharma · October 31, 2020, 3:27pm

I have known the logic of splitting values and transposing them for a while now.

I was around the solution in 5 - 10 minutes but then I checked both your and Melissa’s solution and I thought “ahh never mind I am never going to solve it”, then I imported only 1 row and thought of counting everything manually and regained some confidence. I almost spent an hour easily on this. This DAX is just like that horse

PATHITEM is the strongest leg, SUBSTITUTE is the weakest one!! haha

BrianJ · October 31, 2020, 4:51pm

@Melissa,

This is now driving me crazy. In the Split Column by Delimiter step, Row 3 is the only one that doesn’t parse properly to the end, dropping a blank in column Text.21 instead of ABBB:

For the life of me, I can’t figure out why this is happening, since all the other rows seem to parse fine, and this row parses fine right up until that last value. Any thoughts on what’s going on here?

Thanks.

Brian

BrianJ · October 31, 2020, 5:05pm

AntrikshSharma:

Table = 
VAR Temp =
    ADDCOLUMNS (
        ADDCOLUMNS (
            VALUES ( Ibesmond[TEXT] ),
            "CleanString",
                TRIM (
                    SUBSTITUTE ( SUBSTITUTE ( SUBSTITUTE ( SUBSTITUTE ( SUBSTITUTE ( Ibesmond[TEXT], "$", "^" ), " ", "^" ), ";", " " ), "^", " " ), "AA/BB", "AA BB" )
                )
        ),
        "DelimiterCount",
            1 + LEN ( [CleanString] ) - LEN ( SUBSTITUTE ( [CleanString], " ", "" ) )
    )

Discovering all sorts of gems here. Really like the nested ADDCOLUMNS that allows you to call a virtual column within the same table you created it. I’ve been doing this via sequential variables, but this is much cleaner.

One question - any objection to using the “@” prefix to denote a virtual column to distinguish it from a measure, as advocated by Ferrari? We’re trying to standardize on that approach in the forum solutions involving virtual tables.

Brian

AntrikshSharma · October 31, 2020, 5:09pm

@BrianJ Try this in PQ, logic is similar to SWITCH.

= Table.SplitColumn(#“Added Conditional Column”, “TEXT”, Splitter.SplitTextByAnyDelimiter({"; “, “/”,”;", " “,”$"}))

AntrikshSharma · October 31, 2020, 5:15pm

No objections, I was exhausted and didn’t pay attention to any of the best practices . I still have to rename the variables and think hard if I can come up with something better.

I simply love the “@” for virtual columns.

Melissa · October 31, 2020, 5:30pm

Seems I’ve missed some good conversation going on right here while I was away…

I can report that @AntrikshSharma nailed it
We both missed the delimiter "; “ in the list argument of Splitter.SplitTextByAnyDelimiter

BrianJ · October 31, 2020, 5:43pm

@AntrikshSharma,

Nice catch - thanks!

@ibesmond - Great question, leading to an incredibly interesting and informative thread.

Brian

BrianJ · October 31, 2020, 6:56pm

@Melissa and @AntrikshSharma,

Power Query is not out of the woods yet. I applied @AntrikshSharma’s fix, which correctly now picks up one ABBB, but one AAAA is still not getting picked up relative to DAXosaurus Rex’s results (which are correct). The highlighted row should total 11:

I’ve isolated the problem to Row 2 at the Split by Any Delimiter step:

But again am stuck on why this is happening, and it’s not picking up the second AAAA after the same delimiter as the first…

Brian

eDNA Forum- Count Delimited Text.pbix (22.8 KB)

AntrikshSharma · October 31, 2020, 7:20pm

@BrianJ for the without dates try this:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("lZBBDsMgDAS/gqwekdKmTXvIaf0NlP9/I2ZtQnMIUg4IvGIGm1LkKVmAlFTTmoDYjqPXCq2J5wARVdlykZfz7Sam24a5G7idmuiCSNQoh7MFtlzyZtifVwy6+O+BipB8QrLe+A1DyC4X7Jj2YSyqw9DzpecR9CEbTNI6+JFkte0=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, TEXT = _t, RESULT = _t]),
    #"Removed Columns" = Table.RemoveColumns(Source,{"RESULT"}),
    #"Replaced Value" = Table.ReplaceValue(#"Removed Columns"," ; ","^",Replacer.ReplaceText,{"TEXT"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","/","^",Replacer.ReplaceText,{"TEXT"}),
    #"Replaced Value2" = Table.ReplaceValue(#"Replaced Value1","; ","^",Replacer.ReplaceText,{"TEXT"}),
    #"Replaced Value3" = Table.ReplaceValue(#"Replaced Value2"," ","^",Replacer.ReplaceText,{"TEXT"}),
    #"Replaced Value4" = Table.ReplaceValue(#"Replaced Value3","$","^",Replacer.ReplaceText,{"TEXT"}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Replaced Value4", "TEXT", Splitter.SplitTextByDelimiter("^", QuoteStyle.Csv), {"TEXT.1", "TEXT.2", "TEXT.3", "TEXT.4", "TEXT.5", "TEXT.6", "TEXT.7", "TEXT.8", "TEXT.9", "TEXT.10"}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Split Column by Delimiter", {"ID"}, "Attribute", "Value"),
    #"Filtered Rows" = Table.SelectRows(#"Unpivoted Other Columns", each ([Value] <> "")),
    #"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows",{"Value"}),
    #"Grouped Rows" = Table.Group(#"Removed Other Columns", {"Value"}, {{"Count", each Table.RowCount(_), Int64.Type}})
in
    #"Grouped Rows"

working on the with dates part.

Melissa · October 31, 2020, 7:36pm

@AntrikshSharma and @BrianJ,

I’ve got another solution and that’s using: Table.TransformColumns instead of Table.SplitColumn
I’ll post a full update later.

Table.TransformColumns(
    Source[[TEXT]],{{"TEXT", Splitter.SplitTextByAnyDelimiter({"; ", "/", ";", "$", " "}, QuoteStyle.Csv, false), 
    type text}}
)

AntrikshSharma · October 31, 2020, 7:42pm

@BrianJ Here is a solution for “with dates”:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("lZFdCoQwDISvUoqPQkzrLz4l15De/xo7TVp9WFZYqGjGzsdMe11ximMUCUE1nIEpZUoT70HEBJGzf/qsolVxXcTMqrGMV2QMcFf/NuDv7RH6m5U8Vd1pr+Zj0GfiY1067oE1RUFw0AgBjwOziU8UlZdELdWMKljJj0gN1nBzw90Ve0M6KDES7ttLV5iNsvygvJ+UF4RUCxpnNc7Q3DesU3Cta+3B3y17ks0JPFHmXjmW8gE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, TEXT = _t, RESULT = _t]),
    #"Removed Columns" = Table.RemoveColumns(Source,{"RESULT"}),
    #"Replaced Value" = Table.ReplaceValue(#"Removed Columns"," ; ","^",Replacer.ReplaceText,{"TEXT"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","; ","^",Replacer.ReplaceText,{"TEXT"}),
    #"Replaced Value2" = Table.ReplaceValue(#"Replaced Value1"," ","^",Replacer.ReplaceText,{"TEXT"}),
    #"Replaced Value3" = Table.ReplaceValue(#"Replaced Value2","$","^",Replacer.ReplaceText,{"TEXT"}),
    #"Replaced Value4" = Table.ReplaceValue(#"Replaced Value3",";","^",Replacer.ReplaceText,{"TEXT"}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Replaced Value4", "TEXT", Splitter.SplitTextByDelimiter("^", QuoteStyle.Csv), {"TEXT.1", "TEXT.2", "TEXT.3", "TEXT.4", "TEXT.5", "TEXT.6", "TEXT.7", "TEXT.8", "TEXT.9", "TEXT.10", "TEXT.11", "TEXT.12"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"ID", Int64.Type}, {"TEXT.1", type text}, {"TEXT.2", type text}, {"TEXT.3", type text}, {"TEXT.4", type text}, {"TEXT.5", type text}, {"TEXT.6", type text}, {"TEXT.7", type text}, {"TEXT.8", type text}, {"TEXT.9", type text}, {"TEXT.10", type text}, {"TEXT.11", type text}, {"TEXT.12", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if ( List.Count ( Text.Split([TEXT.1], "/") ) - 1 ) = 1 then Text.Split([TEXT.1], "/"){0} else [TEXT.1]),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each if ( List.Count ( Text.Split([TEXT.1], "/") ) - 1 ) = 1 then Text.Split([TEXT.1], "/"){1} else null),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Custom.2", each if [TEXT.5] = null then null else
if ( List.Count ( Text.Split([TEXT.5], "/") ) - 1 ) = 1 then Text.Split([TEXT.5], "/"){0} else [TEXT.5]),
    #"Added Custom3" = Table.AddColumn(#"Added Custom2", "Custom.3", each if [TEXT.5] = null then null else
if ( List.Count ( Text.Split([TEXT.5], "/") ) - 1 ) = 1 then Text.Split([TEXT.5], "/"){1} else null),
    #"Removed Columns1" = Table.RemoveColumns(#"Added Custom3",{"TEXT.1", "TEXT.5"}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Removed Columns1", {"ID"}, "Attribute", "Value"),
    #"Filtered Rows" = Table.SelectRows(#"Unpivoted Other Columns", each ([Value] <> "")),
    #"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows",{"Value"}),
    #"Grouped Rows" = Table.Group(#"Removed Other Columns", {"Value"}, {{"Count", each Table.RowCount(_), Int64.Type}}),
    #"Sorted Rows" = Table.Sort(#"Grouped Rows",{{"Count", Order.Descending}}),
    #"Added Custom4" = Table.AddColumn(#"Sorted Rows", "Custom", each if Text.StartsWith([Value], "/") then Text.Middle([Value], 1, Text.Length([Value])) else [Value]),
    #"Removed Columns2" = Table.RemoveColumns(#"Added Custom4",{"Value"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns2",{"Custom", "Count"})
in
    #"Reordered Columns"