Forum Discussion
Solved
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 ...
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"
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
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.
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"