How to get all Uppercase Word in raw?

[kellyjkon]

Hi All,

I tried many options such as text.split, delimiter, add custom column from example but I could not succeed. I'm sure the Power Query gurus here will figure this out easily.

My question is actually simple, I want to filter all uppercase words in a line. But since my data came as multiple line, I couldn't separate them with spaces or commas, or I could only get capital letters.
How can I get only the KAMA, WAL, SSSSSS values in the example you see below?

Thanks a lot in advance!

 

[JGordon11]

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    tbl = Table.TransformColumns(Source, {"Column1", each 
            let ts = Text.Split(_, ", "),
                upper = List.Select(ts, (x)=> x=Text.Upper(x))
            in Text.Combine(upper, ", ")})
in
    tbl

Book4
	A	B	C	D
1	Column1		Column1
2	aaa, BBBB, cc, DD, efg, XYZ		BBBB, DD, XYZ
3	DGHS, APPLE, pear, BANANA		DGHS, APPLE, BANANA
4
Sheet2

 

[kellyjkon]

Thanks but comma delimiter doesn't work in my case.
Because as you can see in screenshot my data in multiple line, so, there is no comma separator after KAMA word.
"Bey, hm sp. , KAMA WAL , Tl sp, SSSSSS"

 

[JGordon11]

maybe this

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    tbl = Table.TransformColumns(Source, {"Column1", each 
            let ts = List.RemoveItems(Text.Split(Text.Replace(_," ",","), ","),{""}),
                upper = List.Select(ts, (x)=> x=Text.Upper(x))
            in Text.Combine(upper, ", ")})
in
    tbl

Book1
	A	B	C	D
1	Column1		Column1
2	Bey, hm sp. , KAMA WAL , Tl sp, SSSSSS		KAMA, WAL, SSSSSS
3
Sheet3

 

[kellyjkon]

How can I implement that in power query/power bi? It gave an error when I added it with the Add Column option.

 

[kellyjkon]

As you can see below, There is no space or comma after KAMA. WAL starts with second line in same row. That's my problem because it doesn't identify properly them due to there are no commas or space.

 

[JGordon11]

maybe

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    tbl = Table.TransformColumns(Source, {"Column1", each 
            let lf = Character.FromNumber(10),
                ts = Text.Split(Text.Replace(_,lf,", "), ", "),
                upper = List.Select(ts, (x)=> x=Text.Upper(x))
            in Text.Combine(upper, ", ")})
in
    tbl

Book1
	A	B	C	D
1	Column1		Column1
2	Bey, hm sp. , KAMA WAL, Tl sp, SSSSSS		KAMA, WAL, SSSSSS
3
Sheet3

 

[kellyjkon]

maybe

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    tbl = Table.TransformColumns(Source, {"Column1", each
            let lf = Character.FromNumber(10),
                ts = Text.Split(Text.Replace(_,lf,", "), ", "),
                upper = List.Select(ts, (x)=> x=Text.Upper(x))
            in Text.Combine(upper, ", ")})
in
    tbl

Book1
	A	B	C	D
1	Column1		Column1
2	Bey, hm sp. , KAMA WAL, Tl sp, SSSSSS		KAMA, WAL, SSSSSS
3
Sheet3

So cool! it works now! however, i'm just wondering that you use Character.FromNumber(10) , does it just look at the line after the 10th character or does 10 have another meaning?
because sometimes the length of the first line can be 50 characters or even 5 different multiple lines in a row. The situations below are possible that's why i'm asking.

fale fatal seone crico kulonale fatal seone crico kulo KALAAA
crico kulonale fatal seone crico kulo crico kulonale fatal seone crico kulo fale fatal seone crico kulonale fatal seone crico kulo KALAAA
one crico kulo KALAAA fale fatal seone crico kulonale fatal se

 

[JGordon11]

Character.FromNumber(10) is the line feed character. I am replacing all line feeds in a cell with ", " and then splitting on ", " and then working with that resultant list.

 

[kellyjkon]

You have wisdom, it works thanks!