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
Hi KuntalSingh ,
You can create a new custom column like this:
if [Reference Key 3] = null then [Unique Key] else [Reference Key 3]
Pete
Hi Pete
My ask is
I have two column Unique key and Reference Key 3
Reference key 3 colunm have some blank rows and need reference key on the basis of lookup from colunm Unique key by using M code. If i do it manuall in excel I used formula VLOOKUP([@[Unique_Key]],AA2:AD6546,4,0)
where AA is Unique Key column and AD is Reference Key 3 column