Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
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.
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 ?
Solved! Go to 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
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]
User | Count |
---|---|
63 | |
55 | |
27 | |
16 | |
10 |