Forum Discussion
Creating table using search
Please help in suggesting a solution.
I want to create a Resulting Table that takes an identifier (ID) from Main Table and maps the relevant columns from Secondary Table based on search in ID column.
Secondary table can have any number of rows but the Resulting Table must have IDs from Main Table only.
Thanks in advance!
| Main Table | ID | |
| 123 | ||
| 456 | ||
| 789 | ||
| 246 | ||
| 135 | ||
| 234 |
| Secondary Table | ID | Comment | Date | |
| 123,234 | Test1 | 29-Jan-20 | ||
| 135 | Test2 | 26-Jan-20 | ||
| 123/789 | Test3 | 23-Jan-20 | ||
| 456-135 | Test4 | 20-Jan-20 | ||
| 246 | Test5 | 17-Jan-20 |
| Resulting Table | ID | Comment | Date | |
| 123 | Test1 | 29-Jan-20 | ||
| 123 | Test3 | 23-Jan-20 | ||
| 456 | Test4 | 20-Jan-20 | ||
| 789 | Test3 | 23-Jan-20 | ||
| 246 | Test5 | 17-Jan-20 | ||
| 135 | Test4 | 20-Jan-20 | ||
| 135 | Test2 | 26-Jan-20 | ||
| 234 | Test1 | 29-Jan-20 |
ShubhamBarnwal here you go !!! one thing - this can't be solved in DAX, DAX simply does not have the capacity to split a string based on number consecution.
//function qx let fx=(a)=> Web.Page( "<script> x = '"&a&"'; y=x.match(/\d+/gm) document.write(y); </script>"){0}[Data]{0}[Children]{1}[Children]{0}[Text] in fxmain query - secondary table -ID split on consecutive numbers
let Source = Web.BrowserContents("https://community.powerbi.com/t5/Desktop/Creating-table-using-search/m-p/1632697#M656635"), #"Extracted Table From Html" = Html.Table(Source, {{"Column1", "TABLE:nth-child(7) > * > TR > :nth-child(1)"}, {"Column2", "TABLE:nth-child(7) > * > TR > :nth-child(2)"}, {"Column3", "TABLE:nth-child(7) > * > TR > :nth-child(3)"}, {"Column4", "TABLE:nth-child(7) > * > TR > :nth-child(4)"}, {"Column5", "TABLE:nth-child(7) > * > TR > :nth-child(5)"}}, [RowSelector="TABLE:nth-child(7) > * > TR"]), #"Promoted Headers" = Table.PromoteHeaders(#"Extracted Table From Html", [PromoteAllScalars=true]), #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Secondary Table", type text}, {"", type text}, {"ID", type text}, {"Comment", type text}, {"Date", type date}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each qx([ID])), #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each Text.Split([Custom],",")), #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Custom"}), #"Expanded Custom.1" = Table.ExpandListColumn(#"Removed Columns", "Custom.1") in #"Expanded Custom.1"result
once you are up to here , you can merge this in pqwry or use dax...up to you.
7 Replies
ShubhamBarnwal I would recommend transforming secondary table by splitting id into rows, like splitting on all separator (/, - and comma) and then it will be super simple
Check my latest blog post Compare Budgeted Scenarios vs. Actuals I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!
⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡
parry2k
I thought of that too. But Secondary Table ID column comes from a source that is a free text and can have random data in it too. Say "Entry for ID: 123". So i must specifically search for IDs that are in my Main Table.ShubhamBarnwalcan you spare some details on the ID column of the secondary and what string can it contain and what exactly do you want to extract from that string. E.g. is it goin to be a mixed value string from which you want to extract only consecutive numbers ? Please give some more example of the ID column values
ShubhamBarnwal hmmm it is going to be complicated since there is no pattern as such. Not sure what to say, might be not something I'm going to spend time to solve since it is totally not a model-based approach. Good luck!
