Forum Discussion
replacing value dynamic column Table.ReplaceValue
Hi all,
I am trying to replace blank cell by a Y but the column I want to do this replacement is contained into each of my Section cell ( dynamic). In my original file I have around 3-4000 row, and at least 30 columns, so I cannot really do it manually.
I have seen example to change the content of the future cell depending condition, but I cannot find example where the column name is contained in each row ( and dynamically) .
screenshot show on the left the data I have and on the right the result I'm trying to have.
note that for the moment I'm looking at only column with only exact name, but if someone has a code that works for example like row 4 (S13/S14, impacting column S13 and S14, I take, if not I can do them manaully or add something to duplicate line I think.
Replace value dynamic column
I have tried this :
//test 1//
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCjY0VdJRigRiGBWrAxI1gnKhMlBRY/1gQxNUmUiYFE5jMEwxhfJQTEYViwUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Section = _t, S12 = _t, S13 = _t, S14 = _t, S15 = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Section", type text}, {"S12", type text}, {"S13", type text}, {"S14", type text}, {"S15", type text}}),
FilteredList = List.Buffer(List.Select(Table.ColumnNames(#"Changed Type"), each [Section])),
Replacevalueautocolumn = Table.ReplaceValue(FilteredList,null,"Y",Replacer.ReplaceValue,FilteredList)
in
Replacevalueautocolumn
this
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCjY0VdJRigRiGBWrAxI1gnKhMlBRY/1gQxNUmUiYFE5jMEwxhfJQTEYViwUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Section = _t, S12 = _t, S13 = _t, S14 = _t, S15 = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Section", type text}, {"S12", type text}, {"S13", type text}, {"S14", type text}, {"S15", type text}}),
Replacevalueautocolumn = Table.ReplaceValue(#"Changed Type",null,"Y",Replacer.ReplaceValue,Expression.Evaluate([Section], #shared))
in
Replacevalueautocolumn
//test 3//
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCjY0VdJRigRiGBWrAxI1gnKhMlBRY/1gQxNUmUiYFE5jMEwxhfJQTEYViwUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Section = _t, S12 = _t, S13 = _t, S14 = _t, S15 = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Section", type text}, {"S12", type text}, {"S13", type text}, {"S14", type text}, {"S15", type text}}),
Replacevalueautocolumn = Table.ReplaceValue(#"Changed Type",null,"Y",Replacer.ReplaceValue,[Section])
in
Replacevalueautocolumn
and this
// test 4 > yes ok, I was using snipet of code I didn't understood here, sorry//
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCjY0VdJRigRiGBWrAxI1gnKhMlBRY/1gQxNUmUiYFE5jMEwxhfJQTEYViwUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Section = _t, S12 = _t, S13 = _t, S14 = _t, S15 = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Section", type text}, {"S12", type text}, {"S13", type text}, {"S14", type text}, {"S15", type text}}),
FilteredList = List.Select(Table.ColumnNames(#"Changed Type"), each [Section]),
#"Replaced Value" = Table.FromRecords(
Table.TransformRows(Source, (r) => List.Accumulate(FilteredList, r, (s,c)=>
Record.TransformFields(s,{{c, each if _ = null then 0 else _}})))
, Value.Type(Source)))
in
#"Replaced Value"
i'm afraid I do not know how to link a specific line and a specific column ( I have seen condition on the row, but it was always linked to a named column)
Can you help ?
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCjY0VdJRigRiGBWrAxI1gnKhMlBRY/1gQxNUmUiYFE5jMEwxhfJQTEYViwUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Section = _t, S12 = _t, S13 = _t, S14 = _t, S15 = _t]), Result = Table.FromRecords(List.Transform( Table.ToRecords(Source), (row) => List.Accumulate( Text.Split(row[Section], "/"), row, // [A = 1, B = 2] & [A = 5] => [A = 5, B =2] // [B = 2] & [A = 5] => [A = 5, B =2] (s, v) => s & Record.AddField([], v, "Y") ) )) in Result
3 Replies
- ZhangKunSuper User
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCjY0VdJRigRiGBWrAxI1gnKhMlBRY/1gQxNUmUiYFE5jMEwxhfJQTEYViwUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Section = _t, S12 = _t, S13 = _t, S14 = _t, S15 = _t]), Result = Table.FromRecords(List.Transform( Table.ToRecords(Source), (row) => List.Accumulate( Text.Split(row[Section], "/"), row, // [A = 1, B = 2] & [A = 5] => [A = 5, B =2] // [B = 2] & [A = 5] => [A = 5, B =2] (s, v) => s & Record.AddField([], v, "Y") ) )) in Result- LucileDFrequent Visitor
Perfect, thanks a lot ... just .. can I Know what the comment mean exactly ?
// [A = 1, B = 2] & [A = 5] => [A = 5, B =2] // [B = 2] & [A = 5] => [A = 5, B =2]#noob
- ZhangKunSuper User
https://learn.microsoft.com/en-us/powerquery-m/m-spec-operators#record-merge
[] & [A = 1] => [A =1]
[A =2] & [A =1] => [A = 1]