Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
dogburalHK82
Helper III
Helper III

group by conditions

Hi, 

I have a table below. 

First 2 columns are raw data and 3rd one is what i wish to categorize/group with the following rules.

 

 

Code  Description  Category
FAD001  FADERAL ZH  FADERAL
FAD002  FADERAL DH  FADERAL
FAD003  FADUSO SS  FADUSO
FAD004  FADERAL SH  FADERAL
FAD005  FADERA DD  FADERA
FAD006  FADERALA SS  FADERALA
10  AAA BBB CCC  AAA BBB CCC
20  AAA BBB CCD  AAA BBB CCD
30  AAC DDD EEE  AAC DDD EEE
40  AAA EEE SSD  AAA EEE SSD

 

Rules are 

  1. The Category name is determined by the name until the first space in the Description column. However, if the Code column begins with a number, the full name is included in the category column.

  2. If there are fewer than 2 letters until the first space in the Description, include the name until the second space.

 

Please advise.

 

Thank you

2 ACCEPTED SOLUTIONS
dufoq3
Super User
Super User

Hi @dogburalHK82, is this what are you looking for?

 

Result:

dufoq3_0-1709398985474.png

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Vc89DoAgDIbhqzTMDuDfXijGwUQjumgcPYHeP1ZRwE7kyZsvYV1FgySlEtn9sCN2sLRiy17PE6fEC++z68G5yGWSuySvggNR5Drm+O0oyYiIoLUGY8yD+R/9ROHR8CSBtfbB8isZeNKX536czNMEIx8MfMHvH/58uwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Code = _t, Description = _t]),
    Ad_Category = Table.AddColumn(Source, "Category", each 
     if (try Number.From(Text.Start([Code], 1)) otherwise null) is number //if [Code] starts with number
     then [Description]
     else if Text.Length(Text.BeforeDelimiter([Description], " ")) < 2    //if text length of [Description] before 1st space is len than 2
          then Text.BeforeDelimiter([Description], " ", 1) //return text before 2nd space
          else Text.BeforeDelimiter([Description], " "),   //return text before 1st space
  type text )
in
    Ad_Category

Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

View solution in original post

Hi, no it will ignore such characters. If you want add more, you can do it here (see yellow). In your assignment you ask to check if text lenght before 1st space is less than 2 but I think you need less or equal to 2. You can change it in red circle.

dufoq3_1-1709533103427.png

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bc9NCsIwEAXgqzyyLpL+6H6SiWQhKE3dWLL0BPb+dNLYJoIDA8PH48HMs7oSa92qJh1upBteXsXm613lXHmf/RnuCKHwUMVDFT8fDubClxKnvafVgkQEYwystRt2v5gr+oxWKhnOuQ2HPSkglTm5vD+L8DRhlMFD5vD04T9PH/qTR+qSJRXjCg==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Code = _t, Description = _t]),
    Ad_Category = Table.AddColumn(Source, "Category", each 
     if (try Number.From(Text.Start([Code], 1)) otherwise null) is number //if [Code] starts with number
     then [Description]
     else if Text.Length(Text.BeforeDelimiter(Text.Remove([Description], Text.ToList(".,")), " ")) < 2    //if text length of [Description] before 1st space is len than 2
          then Text.BeforeDelimiter([Description], " ", 1) //return text before 2nd space
          else Text.BeforeDelimiter([Description], " "),   //return text before 1st space
  type text ),
    #"Added Custom" = Table.AddColumn(Ad_Category, "Custom", each Text.Remove([Description], Text.ToList(".,")))
in
    #"Added Custom"

Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

View solution in original post

3 REPLIES 3
dufoq3
Super User
Super User

Hi @dogburalHK82, is this what are you looking for?

 

Result:

dufoq3_0-1709398985474.png

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Vc89DoAgDIbhqzTMDuDfXijGwUQjumgcPYHeP1ZRwE7kyZsvYV1FgySlEtn9sCN2sLRiy17PE6fEC++z68G5yGWSuySvggNR5Drm+O0oyYiIoLUGY8yD+R/9ROHR8CSBtfbB8isZeNKX536czNMEIx8MfMHvH/58uwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Code = _t, Description = _t]),
    Ad_Category = Table.AddColumn(Source, "Category", each 
     if (try Number.From(Text.Start([Code], 1)) otherwise null) is number //if [Code] starts with number
     then [Description]
     else if Text.Length(Text.BeforeDelimiter([Description], " ")) < 2    //if text length of [Description] before 1st space is len than 2
          then Text.BeforeDelimiter([Description], " ", 1) //return text before 2nd space
          else Text.BeforeDelimiter([Description], " "),   //return text before 1st space
  type text )
in
    Ad_Category

Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

Thank you @dufoq3 ,

 

This is perfect. 

Now i would like to how I can write if i would like to igore (.) or (,) when counting text length?

 

Example : H.H DD AA

With the given Power M, it should extract H.H as (.) is being count in text length. 

But If I wish to igore, how should I do?

Hi, no it will ignore such characters. If you want add more, you can do it here (see yellow). In your assignment you ask to check if text lenght before 1st space is less than 2 but I think you need less or equal to 2. You can change it in red circle.

dufoq3_1-1709533103427.png

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bc9NCsIwEAXgqzyyLpL+6H6SiWQhKE3dWLL0BPb+dNLYJoIDA8PH48HMs7oSa92qJh1upBteXsXm613lXHmf/RnuCKHwUMVDFT8fDubClxKnvafVgkQEYwystRt2v5gr+oxWKhnOuQ2HPSkglTm5vD+L8DRhlMFD5vD04T9PH/qTR+qSJRXjCg==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Code = _t, Description = _t]),
    Ad_Category = Table.AddColumn(Source, "Category", each 
     if (try Number.From(Text.Start([Code], 1)) otherwise null) is number //if [Code] starts with number
     then [Description]
     else if Text.Length(Text.BeforeDelimiter(Text.Remove([Description], Text.ToList(".,")), " ")) < 2    //if text length of [Description] before 1st space is len than 2
          then Text.BeforeDelimiter([Description], " ", 1) //return text before 2nd space
          else Text.BeforeDelimiter([Description], " "),   //return text before 1st space
  type text ),
    #"Added Custom" = Table.AddColumn(Ad_Category, "Custom", each Text.Remove([Description], Text.ToList(".,")))
in
    #"Added Custom"

Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

Helpful resources

Announcements
August Power BI Update Carousel

Power BI Monthly Update - August 2025

Check out the August 2025 Power BI update to learn about new features.

August 2025 community update carousel

Fabric Community Update - August 2025

Find out what's new and trending in the Fabric community.

Top Solution Authors