Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Help with extracting string in complicated column

Hi,

 

I would like to request the help with the following problem that I have not been able to solve. I have the following column with data

 

Product Custom Fields
gender=Men;cat=Tops
gender=Men;cat=Tops
gender=Men;cat=Denim;cat=Outerwear
gender=Men;cat=Tops
gender=Men;cat=Outerwear
gender=Men;cat=Denim
gender=Men;cat=Tops
gender=Men;cat=Tops
gender=Men;cat=Outerwear;cat=Tops;"SAPProductCode=Bar & Shield";"ProductLine=Bar & Shield"
gender=Men;cat=Outerwear;SAPProductCode=Vintage;ProductLine=Vintage

 

 

I would like to create a new column called "SAP Product Code" with the data extracted from above. In this case above, the extracted data in the new column would be:

 

Bar & Shield

Vintage

 

I've been looking around the best custom parse or extract for this and have not found an answer yet, and would love any of your help.

 

Thanks you very much!

Ariel

Need Help
  • speedramps's avatar
    speedramps
    3 years ago

    Click here to download a solution 

     

    Try this in Power Query

     

    = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSk/NS0ktsvVNzbNOTiyxDckvKFaK1SFa3CU1LzMXzPIvLUktKk9NLCJFO15NYLNJMY2gLXBV1jFKwY4BAUX5KaXJJc75Kam2TolFCjGlBgZGZgrBGZmpOSkxSkBFUBU+mXlYVeC3DM2GsMy8ksT0VGtkM6FiSrGxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Product Custom Fields" = _t])

     

    Thanks for reaching out for help.

    I put in a lot of effort to help you, now please quickly help me by giving kudos.

    Remeber we are unpaid volunteers and here to coach you with Power BI and DAX skills and techniques, not do the users job for them. So please click the thumbs up and accept as solution button. 

    If you give someone a fish then you only give them one meal, but if you teach them how to fish then they can feed themselves and teach others for a lifetime.  I prefer to teach members on this forum techniques rather give full solutions and do their job. You can then adapt the technique for your solution, learn some DAX skills for next time and soon become a Power BI Super User like me. 

    One question per ticket please. If you need to extend your request then please raise a new ticket.

    You will get a quicker response and each volunteer solver will get the kudos they deserve. Thank you ! 

     

     

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi speedramps, 

     

    Thank you very much for your help! Just looked at the code you've sent and this really helped a lot!

  • speedramps's avatar
    speedramps
    Super User
    3 years ago

    Click here to download a solution 

     

    Try this in Power Query

     

    = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSk/NS0ktsvVNzbNOTiyxDckvKFaK1SFa3CU1LzMXzPIvLUktKk9NLCJFO15NYLNJMY2gLXBV1jFKwY4BAUX5KaXJJc75Kam2TolFCjGlBgZGZgrBGZmpOSkxSkBFUBU+mXlYVeC3DM2GsMy8ksT0VGtkM6FiSrGxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Product Custom Fields" = _t])

     

    Thanks for reaching out for help.

    I put in a lot of effort to help you, now please quickly help me by giving kudos.

    Remeber we are unpaid volunteers and here to coach you with Power BI and DAX skills and techniques, not do the users job for them. So please click the thumbs up and accept as solution button. 

    If you give someone a fish then you only give them one meal, but if you teach them how to fish then they can feed themselves and teach others for a lifetime.  I prefer to teach members on this forum techniques rather give full solutions and do their job. You can then adapt the technique for your solution, learn some DAX skills for next time and soon become a Power BI Super User like me. 

    One question per ticket please. If you need to extend your request then please raise a new ticket.

    You will get a quicker response and each volunteer solver will get the kudos they deserve. Thank you ! 

     

     

  • speedramps's avatar
    speedramps
    Super User
    3 years ago

    Thanks Anonymous for accepting the solution.

     

    Also thanks for the very good problem description and with example input and output data.  Perfect ! 😀😀😀

     

    I hace seen some terrible problem description from other members tonight. ☹️☹️☹️

     

    If you need more help and can provide other clear desciptions

    then please raise a new ticket and quote speedramps anywhere in the body text.

    I will then receive an automatic notification and will try help, unless another superuser beats me to it.