Forum Discussion
Select columns from their position in a table
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
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.
7 Replies
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:
- Getting the column names via Table.ColumnNames
- Counts how many column names there are
- Subtracts 3 from that
- Returns the name of the column desired (8 - 3 in this example)
- 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 - was this helpful? If not, could you explain where it fails to answer your question so I can modify the solution?
Merci
Je vais essayer et je te tiens au courant
Philippe
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
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.
