replacing value dynamic column Table.ReplaceValue

[LucileD]

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 ?

[ZhangKun · Accepted solution]

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

[LucileD]

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 

[ZhangKun]

https://learn.microsoft.com/en-us/powerquery-m/m-spec-operators#record-merge

[] & [A = 1]  => [A =1]

[A =2] & [A =1] => [A = 1]