Creating table using search

[ShubhamBarnwal]

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

[Ashish_Mathur]

Hi,

You may download my PBI file from here.

Hope this helps.

[parry2k]

@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!

[parry2k]

@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.⚡

[ShubhamBarnwal]

@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.

[smpa01]

@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]

@smpa01 
As mentioned, Secondary Table ID column is a free flow text field. We want to extract the info based on search for ID in Main Table.
And yes, secondary table ID column is a mixed value string and we have to search for 3 digit number in that. There can be multiple IDs (3 digit number) in one entry too.

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

[smpa01 · Accepted solution]

@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
    fx

main 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.