Query M code to keep/extract only N-number of last columns and one other column

[kmilarov]

Hi Guys,

I have a query table with multiple columns. I need to filter/keep only few columns based on:

> the first my column should be the one with name : "Name"

> and the  - all 5 columns starting from the columns named: "production number",aaa,bbb,ccc,ddd (delete all after ddd as well )

Keep in mind every refresh can add or delete some columns between Name and production name.

Name	Cat1	cat2	cat3	production number	aaa	bbb	ccc	ddd	TTT
qwert	21	ds	we3	2	1	21	11	22	df

I would like to make it with M code (tried few functions, but no big success). Any ideas?

Thanks

K

[Anonymous]

Hi @kmilarov 

You can try the following solution. you can directly  select the five columns, it will always display the five columns whenever you add many other columns.

Table.SelectColumns(#"Changed Type"(last step name),{"Name", "production number", "aaa", "bbb", "ccc", "ddd"})

You can also refer to the following code in advanced editor 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WKixPLSpR0lEyBmITIDYyBBIpxUCiPBUkZgTEhjBxQzALJJSSphQbCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, test1 = _t, test2 = _t, Cat1 = _t, cat2 = _t, cat3 = _t, #"production number" = _t, aaa = _t, bbb = _t, ccc = _t, ddd = _t, TTT = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"Cat1", Int64.Type}, {"cat2", type text}, {"cat3", type text}, {"production number", Int64.Type}, {"aaa", Int64.Type}, {"bbb", Int64.Type}, {"ccc", Int64.Type}, {"ddd", Int64.Type}, {"TTT", type text}}),
    #"Removed Other Columns" = Table.SelectColumns(#"Changed Type",{"Name", "production number", "aaa", "bbb", "ccc", "ddd"})
in
    #"Removed Other Columns"

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.