Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Index column only for specific value (Conditional index column)

Hello,
 
I am looking for some help with the index column. I have a long column (Column1) with text and want add an index column. The challenge is that the index should only be counted (+1) if in Column1 a specific keyword appears. The keyword is always the same. It should look like this:
         
Column1 | Index
-------------------
Keyword | 1
Text 1     | 
Text 2     |
Keyword | 2
Text 3     |
Text 4     |
Text 5     |
Keyword | 3
Text 6     |
....
 
 
I thought of the following steps: filter Column1 for keyword; apply the function "Index Column"; remove the filter. But I don't know how to remove a filter in M Language.
 
Probably my problem is easy to solve but didn't found a solution in other posts. I hope that someone can help here. Thank you. 
Need Help
  • Nolock's avatar
    Nolock
    7 years ago

    Hi Anonymous,

    I have a solution for you. Click through the steps and you will see how it works.

    I create an index on the original table. Then I create a second table "KeywordRowFullIndex" which contains only "Keyword" rows and create a secondary index on this smaller table. And in the end I join them together on the FullIndex.

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8k6tLM8vSlGK1YlWCkmtKFEwVACBGgWEiBFEBCyAodwYSRIsYIIuYIpPuxlMMhYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    IndexColumn = Table.AddIndexColumn(Source, "FullIndex", 0, 1),
    KeywordRowFullIndex = Table.SelectRows(IndexColumn, each [Column1] = "Keyword"),
    SecondaryIndex = Table.AddIndexColumn(KeywordRowFullIndex, "Index", 1, 1),
    Join = Table.NestedJoin(IndexColumn, "FullIndex", SecondaryIndex, "FullIndex", "SecondaryIndex"),
    ExpandedSecondaryIndex = Table.ExpandTableColumn(Join, "SecondaryIndex", {"Index"}, {"Index"}),
    RemoveFullIndexColumn = Table.RemoveColumns(ExpandedSecondaryIndex,{"FullIndex"})
in
    RemoveFullIndexColumn
  • v-juanli-msft's avatar
    v-juanli-msft
    7 years ago

    Hi Anonymous 

    As tested, Nolock 's solution should work.

    You don't need to filter data, just check my pbix.

     

    Best Regards
    Maggie

     

    Community Support Team _ Maggie Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

4 Replies

  • Nolock's avatar
    Nolock
    Resident Rockstar
    7 years ago

    Hi Anonymous,

    I have a solution for you. Click through the steps and you will see how it works.

    I create an index on the original table. Then I create a second table "KeywordRowFullIndex" which contains only "Keyword" rows and create a secondary index on this smaller table. And in the end I join them together on the FullIndex.

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8k6tLM8vSlGK1YlWCkmtKFEwVACBGgWEiBFEBCyAodwYSRIsYIIuYIpPuxlMMhYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    IndexColumn = Table.AddIndexColumn(Source, "FullIndex", 0, 1),
    KeywordRowFullIndex = Table.SelectRows(IndexColumn, each [Column1] = "Keyword"),
    SecondaryIndex = Table.AddIndexColumn(KeywordRowFullIndex, "Index", 1, 1),
    Join = Table.NestedJoin(IndexColumn, "FullIndex", SecondaryIndex, "FullIndex", "SecondaryIndex"),
    ExpandedSecondaryIndex = Table.ExpandTableColumn(Join, "SecondaryIndex", {"Index"}, {"Index"}),
    RemoveFullIndexColumn = Table.RemoveColumns(ExpandedSecondaryIndex,{"FullIndex"})
in
    RemoveFullIndexColumn
    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Hi Nolock,

       

      Thank you very much for your code, which also provides the index in those rows with the keyword. But unfortunately a lot of rows are deleted in the end, almost every second. Do you have any idea why that is?

      • Nolock's avatar
        Nolock
        Resident Rockstar
        7 years ago

        Hi Anonymous,

        no spontanious idea. Please, can you provide some sample data where it doesn't work?