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

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

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
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

Top Solution Authors
Top Kudoed Authors