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
- BA_Pete2 years agoSuper User
Can you provide a copyable example of all the tables that you're referring to please?
It sounds like you have two separate tables but you've only provided an example of one of them.
You can paste your examples directly from Excel into a reply window here to ensure formatting stays intact.
Pete
- KuntalSingh2 years agoHelper V
I have one table only having multipal column. In Column Reference key 3.1 some Rows are blanks. Need the to lookup values from Unique colunm by using Vlookup and get the values in Reference key 3.1 column
Unique_Key Reference Key 1 Reference Key 2 Reference key 3.1
27689899. 41535261
27689899. 41535261
27585136. 41535253
28316334D138048 41535226
27930964. 41535195
27930964. 41535174
27930964. 41534497
27930802QAUP2223/05466 41524563
278679712022-23/17/CR 41516145
28376909JMV/16/2022-23 41508140
2830830416 41488782
28303756UP2223351 41430718
2825252322-23/32 41382471
275998431060397907 41354750
28092499IOC/VA/011 41244021
27595735GST/KNP/2022/003 41204070
2798337127983371 41154547
2798337127983371 41154529
279917281408 41097136
27958936A-092 41063446
27959015MAU 41062015
279568921408 41059635
27550294VNSDO/04/23-24 40909663
27759530RTS/1254/21-22 40856544
27659853LV/2122/UP/112 40837604
28883364F20000024538
28883364F20000024544
28883336F20000022361- BA_Pete2 years agoSuper User
It sounds like you want to fill in any blank values in the [Reference Key 3.1] column with the value from the [Unique Key] column, but that is exactly what my previous solution did, so why wasn't that what you needed?
Pete