Forum Discussion
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
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.
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
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_CategoryHi, 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"
3 Replies
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_CategoryThank 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.
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"
