Index related to categories (not serial numbers)

[Soog]

I have data containing a lot of text with categories such as the given data.

Then, for traceability, I want to give them addresses in early steps of power query like the desired data, not serial number like index.

Is there any way to go?  Thanks in advance!

Test File 

[ronrsnfld · Accepted solution]

I misunderstood.

You can accomplish what you want with judicious grouping and adding index columns at the desired levels of each grouping.

To group:

•  Duplicate your Category 1 and 2 columns
• Ensure the "blanks" are nulls
• Fill down so every row is appropriately filled
• Group by Category 1 - copy
  • Within the group, group each subtable by Category2 - copy
  • Add an index column to the sub group
  • add an index column to the sub-sub group table
  • add an index column to the main group 
    • Transform address 1 from number to letter
      • If you have more than 26 category1's, you'll need a different algorithm than what I used
    • Expand the necessary columns in the various tables and subtables
    • Transform address 3 to put the number inside parentheses

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("XZHdasMwDIVfxfg6L9GftWMwtjWDXoReKI2wDbYVlDjDb18lXRazCwufjyNZkptG76IL4HWlj2Qknsl3GNUFR3Y4Ietb1Wjhi6FFDhAL9A7eRXLDig4wSnxJTD1CVLUlHi24skrtIOCAK3klFlHpb7FSMrZl7Ar3VwIekZVdbBv/pJhXWVvEfm4GWZopTBcKEXMBdtGkodBXMDmVE+JE5XhvKK8+C+wdd4Og3hkUj4xKPoXWQbkNShxdNOpI01+zJ/D3JeFEjMOofvWWdQCGu5xyumxC/uf8+Jk/6cyY1XzdzHtg2dkT3h4=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Category1 = _t, Category2 = _t, Category3 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Category1", type text}, {"Category2", type text}, {"Category3", type text}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1, Int64.Type),
    #"Duplicated Column" = Table.DuplicateColumn(#"Added Index", "Category1", "Category1 - Copy"),
    #"Replaced Value" = Table.ReplaceValue(#"Duplicated Column","",null,Replacer.ReplaceValue,{"Category1 - Copy"}),
    #"Filled Down" = Table.FillDown(#"Replaced Value",{"Category1 - Copy"}),
    #"Duplicated Column1" = Table.DuplicateColumn(#"Filled Down", "Category2", "Category2 - Copy"),
    #"Replaced Value1" = Table.ReplaceValue(#"Duplicated Column1","",null,Replacer.ReplaceValue,{"Category2 - Copy"}),
    #"Filled Down1" = Table.FillDown(#"Replaced Value1",{"Category2 - Copy"}),

    #"Grouped Rows" = Table.Group(#"Filled Down1", {"Category1 - Copy"}, {
        {"all2", each
            Table.AddIndexColumn( 
                Table.Group(_,{"Category2 - Copy"},{
                    {"all",each Table.AddIndexColumn(_,"Address3",1,1)}
                })
                ,"Address2",1,1)},
        {"all", each _, type table [Category1=nullable text, Category2=nullable text, Category3=nullable text, Index=number, #"Category1 - Copy"=nullable text, #"Category2 - Copy"=nullable text]}}),

    #"Added Index1" = Table.AddIndexColumn(#"Grouped Rows", "Address1", 1, 1, Int64.Type),
    addr1ToAlpha = Table.TransformColumns(#"Added Index1",{"Address1", each Character.FromNumber(_+64)}),
    #"Removed Columns" = Table.RemoveColumns(addr1ToAlpha,{"all", "Category1 - Copy"}),
    #"Expanded all2" = Table.ExpandTableColumn(#"Removed Columns", "all2", {"all", "Address2"}, {"all", "Address2"}),

    #"Expanded all" = Table.ExpandTableColumn(#"Expanded all2", "all", 
        {"Category1", "Category2", "Category3", "Index", "Address3"}, {"Category1", "Category2", "Category3", "Index", "Address3"}),

    addr3WithParentheses = Table.TransformColumns(#"Expanded all",{"Address3",each "(" & Text.From(_) & ")"}),

    #"Reordered Columns" = Table.ReorderColumns(addr3WithParentheses,
        {"Category1", "Category2", "Category3", "Index", "Address1", "Address2", "Address3"})
in
    #"Reordered Columns"

[wdx223_Daniel · Accepted solution]

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Custom1 = #table(
                     Table.ColumnNames(Source)&{"Address1","Address2","Address3"},
                     List.Accumulate(
                                     Table.ToRows(Source),
                                     {{},"",0,0},
                                     (x,y)=>if y{0}<>null then {x{0}&{y&{Text.Start(y{0},1),1,"(1)"}},Text.Start(y{0},1),1,1}
                                            else if y{1}<>null then {x{0}&{y&{x{1},x{2}+1,"(1)"}},x{1},x{2}+1,1}
                                                 else {x{0}&{y&{x{1},x{2},Number.ToText(x{3}+1,"(0)")}},x{1},x{2},x{3}+1}
                                    ){0}
                    )
in
    Custom1

[Soog]

It really smart code! Thank you!

[ronrsnfld]

add an Index Column.  You can do this right from the UI and specify to start at "1" instead of the default "0"

[Soog]

I know the index column.
What I want to do is adding the 3 address columns (right side of the desired data).

[ronrsnfld · Accepted solution]

I misunderstood.

You can accomplish what you want with judicious grouping and adding index columns at the desired levels of each grouping.

To group:

•  Duplicate your Category 1 and 2 columns
• Ensure the "blanks" are nulls
• Fill down so every row is appropriately filled
• Group by Category 1 - copy
  • Within the group, group each subtable by Category2 - copy
  • Add an index column to the sub group
  • add an index column to the sub-sub group table
  • add an index column to the main group 
    • Transform address 1 from number to letter
      • If you have more than 26 category1's, you'll need a different algorithm than what I used
    • Expand the necessary columns in the various tables and subtables
    • Transform address 3 to put the number inside parentheses

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("XZHdasMwDIVfxfg6L9GftWMwtjWDXoReKI2wDbYVlDjDb18lXRazCwufjyNZkptG76IL4HWlj2Qknsl3GNUFR3Y4Ietb1Wjhi6FFDhAL9A7eRXLDig4wSnxJTD1CVLUlHi24skrtIOCAK3klFlHpb7FSMrZl7Ar3VwIekZVdbBv/pJhXWVvEfm4GWZopTBcKEXMBdtGkodBXMDmVE+JE5XhvKK8+C+wdd4Og3hkUj4xKPoXWQbkNShxdNOpI01+zJ/D3JeFEjMOofvWWdQCGu5xyumxC/uf8+Jk/6cyY1XzdzHtg2dkT3h4=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Category1 = _t, Category2 = _t, Category3 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Category1", type text}, {"Category2", type text}, {"Category3", type text}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1, Int64.Type),
    #"Duplicated Column" = Table.DuplicateColumn(#"Added Index", "Category1", "Category1 - Copy"),
    #"Replaced Value" = Table.ReplaceValue(#"Duplicated Column","",null,Replacer.ReplaceValue,{"Category1 - Copy"}),
    #"Filled Down" = Table.FillDown(#"Replaced Value",{"Category1 - Copy"}),
    #"Duplicated Column1" = Table.DuplicateColumn(#"Filled Down", "Category2", "Category2 - Copy"),
    #"Replaced Value1" = Table.ReplaceValue(#"Duplicated Column1","",null,Replacer.ReplaceValue,{"Category2 - Copy"}),
    #"Filled Down1" = Table.FillDown(#"Replaced Value1",{"Category2 - Copy"}),

    #"Grouped Rows" = Table.Group(#"Filled Down1", {"Category1 - Copy"}, {
        {"all2", each
            Table.AddIndexColumn( 
                Table.Group(_,{"Category2 - Copy"},{
                    {"all",each Table.AddIndexColumn(_,"Address3",1,1)}
                })
                ,"Address2",1,1)},
        {"all", each _, type table [Category1=nullable text, Category2=nullable text, Category3=nullable text, Index=number, #"Category1 - Copy"=nullable text, #"Category2 - Copy"=nullable text]}}),

    #"Added Index1" = Table.AddIndexColumn(#"Grouped Rows", "Address1", 1, 1, Int64.Type),
    addr1ToAlpha = Table.TransformColumns(#"Added Index1",{"Address1", each Character.FromNumber(_+64)}),
    #"Removed Columns" = Table.RemoveColumns(addr1ToAlpha,{"all", "Category1 - Copy"}),
    #"Expanded all2" = Table.ExpandTableColumn(#"Removed Columns", "all2", {"all", "Address2"}, {"all", "Address2"}),

    #"Expanded all" = Table.ExpandTableColumn(#"Expanded all2", "all", 
        {"Category1", "Category2", "Category3", "Index", "Address3"}, {"Category1", "Category2", "Category3", "Index", "Address3"}),

    addr3WithParentheses = Table.TransformColumns(#"Expanded all",{"Address3",each "(" & Text.From(_) & ")"}),

    #"Reordered Columns" = Table.ReorderColumns(addr3WithParentheses,
        {"Category1", "Category2", "Category3", "Index", "Address1", "Address2", "Address3"})
in
    #"Reordered Columns"

[Soog]

Thank you. It works perfectly.

Now I have learned a lot from your code!