Forum Discussion

Helper II

3 years ago

Solved

Help to Add a new Column with specific Text from other

I need help, please if anyone can provide a sample file that will help me a lot to understand. I have one lots of column but from column "Style Name" which contain description of products with the Gender in it some time genders is places in first some times in middle and some times at end. so cannot use delimeter.

so should I add a column with poer query or dax

Style Name	Gender
KIDS SHOE ASK VELCRO	KIDS
NATIONAL SHOE ASTRI LOW HE WOMEN	WOMEN
NATINAL SHOE SONDRE FOR WOMEN	WOMEN
SANDAL FRIENDS AO FOR KIDS	KIDS
SANDAL NP BALI 2 JR	KIDS
LADIES CAPRI PANTS 222HP14212 HOUSE	WOMEN
W. BLOUSE 213HP14130, HOUSE	WOMEN
CHILDREN'S BRIEF 3-PACK 230H071815 HOUSE	KIDS
KID S SWIMSUIT 235H032221, RED, 100	KIDS
LADIES MAXI JAQUARD AC-, MAITOKAHVI	WOMEN
LADIES BRIEF 3-PCS AC-NOS-011 ACTUELLE	WOMEN
RUNNING MEN´S JACKET ST-MW-SS22-001	MEN
NOS SOCKS 3 PK BABY BOYS BLUE 234NOS19	BABY

Thank you so much for your help

best regards,

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bZFRbsMgDIavYuU5mYC06vboEDpoCGSYNKuyHmM32wl2sjldG7VSnwDzWf+Hmeeicy0B2WgAqYOj8TrForyUi3M5FwGziwH9jcnJgY8TWANT7E1g9n+9wStLMbTJwD6mJyRhaBncJ2cCC2C8cJfUu/ArFQZo0DtQcEj31x5bZwg0Diw1YMgESik7yI2SCmwcyTyETi/Q+KUKStYLJmtRPuG0dZ7Vw9e3EGpH0LDlHupqQN2BqoUVO/kqt2vnKsQb4GlOrqfRZUa3VtSsJEtIpi1BCvHEv8dPBwf8GDG1gLoqueJy7NAe3YPXlV91NC14iFQJKXmbR+P941PSGIIL78Dn3x/iFN2ZDJSrfqqIlKqEkNywfmBk/ag7ghqGjqfenKCJJ870I0+t3jAg34A7lqvifP4D", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Style Name" = _t, Gender = _t]),
    Custom1 = Table.AddColumn(
                              Source,
                              "GenderNew",
                              each List.Skip(
                                             {
                                              {{"BABY"},"BABY"},
                                              {{"LADIES","L.","W.","WOMEN"},"WOMEN"},
                                              {{"MEN'S","MEN´S"},"MEN"},
                                              {{"CHILDREN'S","KIDS","JR","KID "},"KIDS"},
                                              {{"BOYS"},"BOYS"}
                                             },
                                             (x)=>not List.Contains(x{0},[Style Name],(x,y)=>Text.Contains(y,x,Comparer.OrdinalIgnoreCase))
                                            ){0}?{1}? ??"OTHERS")
in
    Custom1

you can update the list in the first parameter of List.Zip

{{the list of text which you want to category to group 1},"group1"},{the list of text which you want to category to group 2},"group2"}.......as many as you want}

1 Reply

Community Champion

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bZFRbsMgDIavYuU5mYC06vboEDpoCGSYNKuyHmM32wl2sjldG7VSnwDzWf+Hmeeicy0B2WgAqYOj8TrForyUi3M5FwGziwH9jcnJgY8TWANT7E1g9n+9wStLMbTJwD6mJyRhaBncJ2cCC2C8cJfUu/ArFQZo0DtQcEj31x5bZwg0Diw1YMgESik7yI2SCmwcyTyETi/Q+KUKStYLJmtRPuG0dZ7Vw9e3EGpH0LDlHupqQN2BqoUVO/kqt2vnKsQb4GlOrqfRZUa3VtSsJEtIpi1BCvHEv8dPBwf8GDG1gLoqueJy7NAe3YPXlV91NC14iFQJKXmbR+P941PSGIIL78Dn3x/iFN2ZDJSrfqqIlKqEkNywfmBk/ag7ghqGjqfenKCJJ870I0+t3jAg34A7lqvifP4D", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Style Name" = _t, Gender = _t]),
    Custom1 = Table.AddColumn(
                              Source,
                              "GenderNew",
                              each List.Skip(
                                             {
                                              {{"BABY"},"BABY"},
                                              {{"LADIES","L.","W.","WOMEN"},"WOMEN"},
                                              {{"MEN'S","MEN´S"},"MEN"},
                                              {{"CHILDREN'S","KIDS","JR","KID "},"KIDS"},
                                              {{"BOYS"},"BOYS"}
                                             },
                                             (x)=>not List.Contains(x{0},[Style Name],(x,y)=>Text.Contains(y,x,Comparer.OrdinalIgnoreCase))
                                            ){0}?{1}? ??"OTHERS")
in
    Custom1

you can update the list in the first parameter of List.Zip

{{the list of text which you want to category to group 1},"group1"},{the list of text which you want to category to group 2},"group2"}.......as many as you want}

Related content