Forum Discussion

Abbi's avatar
Abbi
Icon for Helper I rankHelper I
4 years ago
Solved

How to Split a specific value from a column

Hi All,   We have a column named Tags against each ID which contains the bill numbers. Have to pull out the bill numbers against each ID from tags column. Bill number doesn't have standard format. ...
  • Greg_Deckler's avatar
    Greg_Deckler
    4 years ago

    Abbi Still some clean up to do but this should get you started:

    Bill number = 
  VAR __Text = [Tags]
  VAR __1stBill = SEARCH("bill", __Text,,0)
  VAR __1stSemi = IF(IF(__1stBill<>0,SEARCH(";", __Text, __1stBill,0),0)=0,LEN(__Text)+1,SEARCH(";", __Text, __1stBill,0))
  VAR __2ndBill = IF(__1stSemi<>0,SEARCH("bill", __Text,__1stSemi,0),0)
  VAR __2ndSemi = IF(IF(__2ndBill<>0,SEARCH(";", __Text, __2ndBill,0),0)=0,LEN(__Text)+1,SEARCH(";", __Text, __2ndBill,0))
  VAR __1stBillNum = IF(__1stBill=0,BLANK(),MID(__Text,__1stBill+4,__1stSemi-__1stBill-4))
  VAR __2ndBillNum = IF(__2ndBill=0,BLANK(),MID(__Text,__2ndBill+4,__2ndSemi - __2ndBill - 4))
  VAR __1stTales = SEARCH("tales:", __Text,,0)
  VAR __1stTalesSemi = IF(IF(__1stBill<>0,SEARCH(";", __Text, __1stTales,0),0)=0,LEN(__Text)+1,SEARCH(";", __Text, __1stTales,0))
  VAR __1stTalesNum = IF(__1stTales=0,BLANK(),MID(__Text,__1stTales+6,__1stTalesSemi - __1stTales - 6))
RETURN
    SWITCH(TRUE(),
        ISBLANK(__1stTalesNum) && ISBLANK(__2ndBillNum),__1stBillNum,
        ISBLANK(__1stTalesNum) && NOT(ISBLANK(__2ndBillNum)),__1stBillNum & ";" & __2ndBillNum,
        ISBLANK(__2ndBillNum) && NOT(ISBLANK(__1stTalesNum)),__1stBillNum & ";" & __1stTalesNum,
        __1stBillNum & ";" & __2ndBillNum & ";" & __1stTalesNum
    )
Greg_Deckler's avatar
Greg_Deckler
Icon for Community Champion rankCommunity Champion
4 years ago

Abbi You didn't post sample data as text so you may get a few syntax errors because I can't test this but try:

Bill number column =
  VAR __Text = [Tags]
  VAR __1stBill = SEARCH("bill", __Text,,BLANK())
  VAR __1stSemi = SEARCH(";", __Text, __1stBill,BLANK())
  VAR __2ndBill = SEARCH("bill", __Text,__1stSemi,BLANK())
  VAR __2ndSemi = SEARCH(";", __Text, __2ndBill,BLANK())
  VAR __1stBillNum = IF(ISBLANK(__1stBill),BLANK(),MID(__Text,__1stBill+4,__1stSemi - 1stBill - 4))
  VAR __2ndBillNum = IF(ISBLANK(__2ndBill),BLANK(),MID(__Text,__2ndBill+4,__2ndSemi - 2ndBill - 4))
RETURN
  IF(ISBLANK(__2ndBillNum),__1stBillNum, __1stBillNum & ";" & __2ndBillNum)
  • Abbi's avatar
    Abbi
    Icon for Helper I rankHelper I
    4 years ago

    Greg_Deckler  thanks for looking at my post. Pasting down the data for you to explore. I modified the data. Please help me out.

     

    IDTagsBill number
    456324Salt:Bill3334223; shoe;Paper;Note;64283334223
    43548Chain;Keys;Socks:BILL7685;Tomato;Billdetails7685
    545666Figs;Pops;Goods: Bill3768237682
    32466Hats;Yogurt: BiLL 21889; Salt:Bill4820521889;48205
    3350987Fanta;Clips;Onti:bill 23487;Billonfine23487
    34228Sause;Grapes;Pads 
    58791Napkens;Thread:Bill 396742;Eggs;Trays: BILL 341730396742;3411730
    876537Bowl;Jackets: Bill23710;gloves:Bill365901;Tales:61047223710;365901;610472
    • Greg_Deckler's avatar
      Greg_Deckler
      Icon for Community Champion rankCommunity Champion
      4 years ago

      Abbi Still some clean up to do but this should get you started:

      Bill number = 
  VAR __Text = [Tags]
  VAR __1stBill = SEARCH("bill", __Text,,0)
  VAR __1stSemi = IF(IF(__1stBill<>0,SEARCH(";", __Text, __1stBill,0),0)=0,LEN(__Text)+1,SEARCH(";", __Text, __1stBill,0))
  VAR __2ndBill = IF(__1stSemi<>0,SEARCH("bill", __Text,__1stSemi,0),0)
  VAR __2ndSemi = IF(IF(__2ndBill<>0,SEARCH(";", __Text, __2ndBill,0),0)=0,LEN(__Text)+1,SEARCH(";", __Text, __2ndBill,0))
  VAR __1stBillNum = IF(__1stBill=0,BLANK(),MID(__Text,__1stBill+4,__1stSemi-__1stBill-4))
  VAR __2ndBillNum = IF(__2ndBill=0,BLANK(),MID(__Text,__2ndBill+4,__2ndSemi - __2ndBill - 4))
  VAR __1stTales = SEARCH("tales:", __Text,,0)
  VAR __1stTalesSemi = IF(IF(__1stBill<>0,SEARCH(";", __Text, __1stTales,0),0)=0,LEN(__Text)+1,SEARCH(";", __Text, __1stTales,0))
  VAR __1stTalesNum = IF(__1stTales=0,BLANK(),MID(__Text,__1stTales+6,__1stTalesSemi - __1stTales - 6))
RETURN
    SWITCH(TRUE(),
        ISBLANK(__1stTalesNum) && ISBLANK(__2ndBillNum),__1stBillNum,
        ISBLANK(__1stTalesNum) && NOT(ISBLANK(__2ndBillNum)),__1stBillNum & ";" & __2ndBillNum,
        ISBLANK(__2ndBillNum) && NOT(ISBLANK(__1stTalesNum)),__1stBillNum & ";" & __1stTalesNum,
        __1stBillNum & ";" & __2ndBillNum & ";" & __1stTalesNum
    )
      • Abbi's avatar
        Abbi
        Icon for Helper I rankHelper I
        4 years ago

        Greg_Deckler  the above DAX formula is working good, but when i have Bill number in Tags for more than twice then we will be missing all the bill number after 2nd occurences. So can we re-model the formula so that any number occurences of Bill number can acheived. 

    • smpa01's avatar
      smpa01
      Icon for Community Champion rankCommunity Champion
      4 years ago

      Abbi  you can achieve this with native PQ

       

      let
regex=
let   
fx=(input)=>
        Web.Page(
            "<script>
                var x='"&input&"'; // this is the input string for regex
                var b=x.match(/bill\d+|bill \d+/gmi); // specify the desired regular expression inside string.match()
                                        //https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/String/match 
                var c =b.toString().replace(/bill|bill /gmi,'')                        
                document.write(c);
            </script>"){0}[Data]{0}[Children]{1}[Children]{0}[Text]

    in
fx,
Source = Web.BrowserContents("https://community.powerbi.com/t5/DAX-Commands-and-Tips/How-to-Split-a-specific-value-from-a-column/m-p/2199869#M51627"),
#"Extracted Table From Html" = Html.Table(Source, {{"Column1", "TABLE:nth-child(3) > * > TR > :nth-child(1)"}, {"Column2", "TABLE:nth-child(3) > * > TR > :nth-child(2)"}, {"Column3", "TABLE:nth-child(3) > * > TR > :nth-child(3)"}}, [RowSelector="TABLE:nth-child(3) > * > TR"]),
#"Promoted Headers" = Table.PromoteHeaders(#"Extracted Table From Html", [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"ID", Int64.Type}, {"Tags", type text}, {"Bill number", type text}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Bill number"}),
#"Added Custom" = Table.AddColumn(#"Removed Columns", "Bill Number", each try regex([Tags]) otherwise null)
in
#"Added Custom"

       

       

      https://community.powerbi.com/t5/Community-Blog/Using-JavaScript-in-power-query-for-regex-Part2/ba-p/2132960

      https://community.powerbi.com/t5/Community-Blog/How-to-use-JavaScript-inside-power-query-for-data-extraction/ba-p/1632844

       

       

      • CNENFRNL's avatar
        CNENFRNL
        Icon for Community Champion rankCommunity Champion
        4 years ago

        Apart from PQ, this regex pattern is easier,

        /(?<=bill) *\d+/gmi

        The following snippet of js code works well,

        <Script>
	var regex = /(?<=bill) *\d+/gmi;
	var str = 'Hats;Yogurt: BiLL 21889; Salt:Bill48205';
	var matches = str.matchAll(regex);
	
	console.log(Array.from(matches, x => x[0].trim()).join('; '))

</Script>

        but it throws error when embedded in PQ. PQ doesn't support embedded js with lookbehind assertion, I'm afraid; but I didn't find any official documentation in this regard. Pls share it if you find.

         

        On top of regex, I'd use native functions in this simple scenario,

        let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("PZDLbsIwEEV/xcq6i8TvZHaglj4iikQ2FWLhEjdYuDGKTSv+vhODurXvmblndruCC8koLx6KrfGpWTjvGWOcUgYkHoOFjTnbCdYhWZCc6mL/gAwTXCOyPBo3wpu9RtiGwyk2i5e2VVIL6MK3SQHmcb1NxvmYQYHbpETyyQ0RNuEcYRVCHxuSFyNKcw4b5dizSRE+wnCZ0hxpW0IrrWsg/2W5pqW4MUyUtVbzcDMmA0vvcPz7mFzziUFCGdcqNwrjlxvtDUJTneUv0cJqQlnsZfp7Xa3qCn/X5nyyY4TuOFnT572E1VJxCo8DinSTuc4OaE8YrxQrM66VFGwutAi/Hl7N4WTTXZUyVZUw+PBj4+3oUtRlBZ3x+CCrkiu8xP4P", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Tags = _t]),

    Bills = Table.AddColumn(Source, "Bills", each Text.Combine(List.Accumulate(Text.Split([Tags], ";"), {}, (s,c) => let num = if Text.Contains(c, "bill", Comparer.OrdinalIgnoreCase) then Text.Select(c, {"0".."9"}) else "" in if Text.Length(num) > 0 then s & {num} else s), "; "))
in
    Bills