Indexing a Table of Tables with custom Textual values

[Anonymous]

I have a Table of Tables:

Custom

Table

Table

Inside each Table, it has the following Format:

Table at index 0:

ID	Col1	Col2
123	sdds	rgfg
234	lsfg	ldfg

Table at index 1:

ID	Col1	Col2
123	ksd	woed
234	rod	mkd

I want to add a custom column to each table such that,

1st table at index 0 will have the new column values as "AAA" and 

2nd table at index 1 will have the new column values as "BBB". So resultant tables will look like this:

Custom

Table

Table

Table at index 0:

ID	Col1	Col2	Metric
123	sdds	rgfg	AAA
234	lsfg	ldfg	AAA

Table at index 1:

ID	Col1	Col2	Metric
123	ksd	woed	BBB
234	rod	mkd	BBB

Is this possible with pure, concise M-code?

[Anonymous]

@Jimmy801 ,

What i meant is the text in the Metric column could be anything for each of the tables. Later i want to combine and Pivot the tables on this `Metric` column. Can it not be something shorter like what List.Positions gives?

e.g. List.Positions([Custom]) i.e. a 0 for 1st table & a 1 for 2nd table.

I can't add an Index column, because then each table rows will have the same set of index numbers.

Custom	Metric
Table	0
Table	1

So if i add another Conditional column based on Metric column, then i could do something like:

Table.AddColumn([Metric], each if [Metric]=0 then "UNIQ" else "ND" )

Custom	Metric
Table	UNIQ
Table	ND

Is there a shorter code to do something like this? I am trying to reduce the number of lines of code inorder for the query to execute quickly (optimize it).

[Jimmy801]

Hello @Anonymous 

somehow you have to create an Index... automatically or manually, then add a Index to your table-column and then somehow add a new column to every single table, using you index-table. If you are satisfied with a metric of 1, 2 etc. then you can use a shorter code like this

let
    TableA = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyVtJRKk5JKQZSRelp6UqxOtFKRsYmQG5OMZALpFJAorEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Col1 = _t, Col2 = _t]),
    TableB = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyVtJRKk5JKQZSRelp6UqxOtFKRsYmQG5OMZALpFJAorEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Col1 = _t, Col2 = _t]),
    TableList = {TableA, TableB},
    #"Converted to Table" = Table.FromList(TableList, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Added Index" = Table.AddIndexColumn(#"Converted to Table", "Index", 1, 1),

    AddColumnToTable = Table.ReplaceValue
    (
        #"Added Index",
        each [Column1],
        (tbl)=> Table.AddColumn(tbl[Column1], "Metric", each tbl[Index]),
        Replacer.ReplaceValue,
        {"Column1"}
    ),
    #"Removed Other Columns" = Table.SelectColumns(AddColumnToTable,{"Column1"}),
    Column1 = #"Removed Other Columns"[Column1]
in
    Column1

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

[Anonymous]

@Jimmy801 i revisited your earlier code and actually you have given room for improvement. 🙂

I clubbed the steps into 2 steps like this : 

let
    TableA = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyVtJRKk5JKQZSRelp6UqxOtFKRsYmQG5OMZALpFJAorEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Col1 = _t, Col2 = _t]),
    TableB = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyVtJRKk5JKQZSRelp6UqxOtFKRsYmQG5OMZALpFJAorEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Col1 = _t, Col2 = _t]),
    TableList = {TableA, TableB},
    ConvertedToTable = Table.FromList(TableList, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    ConvertIndex = Table.TransformColumns( Table.AddIndexColumn(ConvertedToTable, "Index", 0, 1), { {"Index", each {"UNIQ","ND"}{_} } } ),
    AddColumnToTable = Table.Combine( Table.SelectColumns( Table.ReplaceValue ( ConvertIndex, each [Column1], (tbl) => Table.AddColumn(tbl[Column1], "Metric", each tbl[Index]), Replacer.ReplaceValue, {"Column1"} ), {"Column1"})[Column1] )
in
    AddColumnToTable

I especially liked this step where you are adding index, then transforming and later replacing the index values with textual ones inside each table.

= Table.TransformColumns( Table.AddIndexColumn(ConvertedToTable, "Index", 0, 1), { {"Index", each {"UNIQ","ND"}{_} } } )

[Jimmy801 · Accepted solution]

Hello @Anonymous 

try this solution.

Creates an Index with AAA, BBB to ZZZ, adds a Index-Column to your table an transforms it to your index logic. Then this new index-column in integrated in your tables with a Table.ReplaceValue

let
    TableA = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyVtJRKk5JKQZSRelp6UqxOtFKRsYmQG5OMZALpFJAorEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Col1 = _t, Col2 = _t]),
    TableB = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyVtJRKk5JKQZSRelp6UqxOtFKRsYmQG5OMZALpFJAorEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Col1 = _t, Col2 = _t]),
    TableList = {TableA, TableB},
    #"Converted to Table" = Table.FromList(TableList, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    CreateIndex = List.Transform({"A".."Z"}, each Text.Combine(List.Repeat({_}, 3))),
    #"Added Index" = Table.AddIndexColumn(#"Converted to Table", "Index", 0, 1),
    ConvertIndex = Table.TransformColumns
    (
        #"Added Index", 
        {
            {
                "Index",
                each CreateIndex{_}
            }
        }
    ),
    AddColumnToTable = Table.ReplaceValue
    (
        ConvertIndex,
        each [Column1],
        (tbl)=> Table.AddColumn(tbl[Column1], "Metric", each tbl[Index]),
        Replacer.ReplaceValue,
        {"Column1"}
    ),
    #"Removed Other Columns" = Table.SelectColumns(AddColumnToTable,{"Column1"}),
    Column1 = #"Removed Other Columns"[Column1]
in
    Column1

Copy paste this code to the advanced editor in a new blank query to see how the solution works.

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy