Forum Discussion
Need Help on Excel power query editor by using M code
- 2 years ago
Ok. This code takes the highest [Reference ky 3] value to fill in the blanks:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jZDLCsIwFER/Rbou9Wbueym4duG29P9/w9S0IJhaV4GcORMm8zzgcb88b1dEgjPKfg7jIGAJLjQs46/YCd5a9B0rmoCbUQo2lsnRY62WJioTCIzQUIRVoikm7CJ2nGmNlaBrC6vrcebUdmo2xcfbbjW3212y2qHsaIudrE4OQQSjYS9af4u7+P/LTpH0HPlytKzLlhc=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Unique Key" = _t, #"Reference key 3" = _t]), repBlankNull = Table.ReplaceValue(Source,"",null,Replacer.ReplaceValue,{"Reference key 3"}), //Relevant steps from here ------> groupRows = Table.Group(repBlankNull, {"Unique Key"}, {{"data", each _, type table [Unique Key=nullable text, Reference key 3=nullable text]}}), addNestedRef3 = Table.TransformColumns( groupRows, { "data", (x)=> Table.AddColumn( x, "RefKey3", each if [Reference key 3] = null then List.Max(x[Reference key 3]) else [Reference key 3] ) } ), expandData = Table.ExpandTableColumn(addNestedRef3, "data", {"Reference key 3", "RefKey3"}, {"Reference key 3", "RefKey3"}) in expandDataSummary:
-1- groupRows = Group By [Unique Ref] and add an All Rows aggregated column called "data"
-2- addNestedRef3 = Add a column to the nested table that gets the highest [Referece key 3] value if the cell is null
-3- expandData = Expand the nested table columns back out again
Example query output:
Pete
Ok. This code takes the highest [Reference ky 3] value to fill in the blanks:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jZDLCsIwFER/Rbou9Wbueym4duG29P9/w9S0IJhaV4GcORMm8zzgcb88b1dEgjPKfg7jIGAJLjQs46/YCd5a9B0rmoCbUQo2lsnRY62WJioTCIzQUIRVoikm7CJ2nGmNlaBrC6vrcebUdmo2xcfbbjW3212y2qHsaIudrE4OQQSjYS9af4u7+P/LTpH0HPlytKzLlhc=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Unique Key" = _t, #"Reference key 3" = _t]),
repBlankNull = Table.ReplaceValue(Source,"",null,Replacer.ReplaceValue,{"Reference key 3"}),
//Relevant steps from here ------>
groupRows = Table.Group(repBlankNull, {"Unique Key"}, {{"data", each _, type table [Unique Key=nullable text, Reference key 3=nullable text]}}),
addNestedRef3 =
Table.TransformColumns(
groupRows,
{
"data",
(x)=> Table.AddColumn(
x,
"RefKey3",
each if [Reference key 3] = null then List.Max(x[Reference key 3])
else [Reference key 3]
)
}
),
expandData = Table.ExpandTableColumn(addNestedRef3, "data", {"Reference key 3", "RefKey3"}, {"Reference key 3", "RefKey3"})
in
expandData
Summary:
-1- groupRows = Group By [Unique Ref] and add an All Rows aggregated column called "data"
-2- addNestedRef3 = Add a column to the nested table that gets the highest [Referece key 3] value if the cell is null
-3- expandData = Expand the nested table columns back out again
Example query output:
Pete
Thanks! Pete