Find the missing letters in the letter sequence with Power Query

[gargamalebarbosa]

Hi,
How can i find missing letters in the letter sequence with power query ?

Thank you ,

 

[GraH]

Extra first and last char from the string. Put in into a list, like {"a".."f"}. They split the string to a list with Text.ToList. finally use List.Difference({first list, secondlist}).

 

[gargamalebarbosa]

Extra first and last char from the string. Put in into a list, like {"a".."f"}. They split the string to a list with Text.ToList. finally use List.Difference({first list, secondlist}).

Thnak you for answer. I'm not good at M code. I will try, i hope i can do it.

 

[GraH]

Quick and dirty...

Book1
	A
1	List
2	abcef
3	jln
4	www
5	af
6	suwz
7	qrst
8	filo
Sheet1

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"List", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "As List", each List.Distinct(Text.ToList([List]))),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Full list", each List.Distinct({List.First([As List]).. List.Last([As List])})),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Difference", each Text.Combine(List.Difference([Full list], [As List]), ", "), type text),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom2",{"As List", "Full list"})
in
    #"Removed Columns"

Book1
	A	B
1	List	Difference
2	abcef	d
3	jln	k, m
4	www
5	af	b, c, d, e
6	suwz	t, v, x, y
7	qrst
8	filo	g, h, j, k, m, n
Table1

 

[gargamalebarbosa]

@GraH Thank you for taking the time to share your knowledge. Last question : We have some special character in our alphabet. Example : ç-ğ-ü-ş
how can ADD them to FULL LIST ?

 

[gargamalebarbosa]

I found a solution.. but it was not right .

example :It should be like that :

 

[GraH]

It requires an alternative approach.

// List called ABC
= {"a", "b", "c", "ç", "d", "e", "f", "g", "ğ", "h", "i", "j", "k", "l", "m", "n", "o", "p", "q", "r", "s", "ş", "t", "u", "ü", "v", "w", "x", "y", "z"}

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"List", type text}}),
    String_as_list = Table.AddColumn(#"Changed Type", "As list", each List.Distinct(Text.ToList([List]))),
    Full_list = Table.AddColumn(String_as_list, "Full list", each List.Range(ABC, List.PositionOf(ABC, List.First([As list])), List.PositionOf(ABC, List.Last([As list]))-List.PositionOf(ABC, List.First([As list])))),
    #"Added Custom2" = Table.AddColumn(Full_list, "Difference", each Text.Combine(List.Difference([Full list], [As list]), ", "), type text),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom2",{"As list", "Full list"})
in
    #"Removed Columns"

Book1
	I	J
1	List	Difference
2	abcef	ç, d
3	jln	k, m
4	www
5	af	b, c, ç, d, e
6	suwz	ş, t, ü, v, x, y
7	qrst	ş
8	filo	g, ğ, h, j, k, m, n
Sheet1

 

[gargamalebarbosa]

Thank you for your support. And your soution is perfect.
I coudn't add "List call ABC" to query. Is this a new CUMSTOM COLUMN ? How can I add to query ?
Sorry i disturbed you so much.

 

[gargamalebarbosa]

OK! i found it. Thank you so much again.

 

[GraH]

Thank you for the mark and the feedback. Nice to read you figured it out on your own.