Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
LucileD
Frequent Visitor

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 columnReplace 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 ?

1 ACCEPTED SOLUTION
ZhangKun
Super User
Super 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

View solution in original post

3 REPLIES 3
ZhangKun
Super User
Super 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

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 

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

 

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

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

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.