Forum Discussion
Creating table using search
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.
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 |
Community ChampionShubhamBarnwal 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.