Solved! Go to Solution.
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
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.
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
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?
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.
Hi @Anonymous,
no spontanious idea. Please, can you provide some sample data where it doesn't work?