group by conditions

[dogburalHK82]

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

[dufoq3 · Accepted solution]

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

Result:

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

[dufoq3 · Accepted solution]

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

Result:

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

[dogburalHK82]

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?

[dufoq3 · Accepted solution]

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.

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"