Select columns from their position in a table

[PhilippeMuniesa]

Hello..

on power Query

I want to replace null with 0 from the third column to the last column

or from the third column to a Y position column in the table.

I've tried several methods, but it still gets stuck

thanks very much

[edhans]

Hi @PhilippeMuniesa - Take a look at this. It replaces blank - "" - with a 0. Change it to null in your code.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTIGYhMgNgViMyA2B2ILILZUitWJBstiQbGxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t, Column5 = _t, Column6 = _t, Column7 = _t, Column8 = _t]),
    #"Replaced Value" = Table.ReplaceValue(Source,"","0",Replacer.ReplaceValue,{Table.ColumnNames(Source){List.Count(Table.ColumnNames(Source)) - 3}})
in
    #"Replaced Value"

It does this by:

1. Getting the column names via Table.ColumnNames
2. Counts how many column names there are
3. Subtracts 3 from that
4. Returns the name of the column desired (8 - 3 in this example)
5. Uses that column name in the Table.ReplaceValue() function.

How to use M code provided in a blank query:
1) In Power Query, select New Source, then Blank Query
2) On the Home ribbon, select "Advanced Editor" button
3) Remove everything you see, then paste the M code I've given you in that box.
4) Press Done
5) See this article if you need help using this M code in your model.

[PhilippeMuniesa]

Hello,

that is not exactly what I want. I would replace "null" by  "0" from the  column 3 up to the last column and therefore that the result muste be:

Column1	2	3	4	5	6	...	...	Last
		0	0	0	0	0	0	0

Thank you from your Help

Philippe Muniesa

[edhans · Accepted solution]

Here you go. Instead of picking the 3rd from last, I kept the last 3.

EDIT: Just reread your post - you want from 3-n. The M code below does that. I didn't redo the screen shot, but it works. Coluimn 1 and 2 unaffected. 3-n will all be 0 now.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTIGYhMgNgViMyA2B2ILILZUitWJBstiQSApSxxSsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t, Column5 = _t, Column6 = _t, Column7 = _t, Column8 = _t]),
    #"Last Columns" = 
        List.Skip(
                Table.ColumnNames(Source),
                2
                ),
    #"Replace Values" = Table.ReplaceValue(Source,"",0,Replacer.ReplaceValue,#"Last Columns")
in
    #"Replace Values"

It returns this:

Note I am using Replacer.ReplaceValue. You may need to use Replacer.ReplaceText and replace with "0" instead of 0, depending on how your data type is set.

Let me know if that works @PhilippeMuniesa 

How to use M code provided in a blank query:
1) In Power Query, select New Source, then Blank Query
2) On the Home ribbon, select "Advanced Editor" button
3) Remove everything you see, then paste the M code I've given you in that box.
4) Press Done
5) See this article if you need help using this M code in your model.

[PhilippeMuniesa]

Thnak you,

That is another solution, compared to others that have been provided to me. 

it works perfectly

Philippe

[edhans]

Great @PhilippeMuniesa - glad I was able to assist. 

[PhilippeMuniesa]

Merci

Je vais essayer et je te tiens au courant

Philippe

[edhans]

@PhilippeMuniesa - was this helpful? If not, could you explain where it fails to answer your question so I can modify the solution?