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
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
No,
Lookup unique_key to return the reference key 3 where the ref key are blank.
If we do it manually used Vlookup formula: =Vlookup (Unique_Key,Rance( Unique_Key & reference key3 ) Column number in the range containing the return value, 0).
Hope you understand my ask
- KuntalSingh2 years agoHelper V
Sample Data,
Unique Key Reference Key 1 Reference Key 2 Reference key 3 2ND RA/2892398128923981 42348310 2ND RA/2892398128923981 42348313 2ND RA/2892398128923981 42348315 00328980146 42348471 NMPPL-SIK/OFF/RT27916712 42349073 NMPPL-SIK/OFF/RT27917080 42349107 159227660942 42349938 159227660942 42349952 ATI/23-24/4628422284 42350916 8321127883461 5943992158 20.01.202328585286 5946437446 20.01.202328585286 5946437658 10.02.202328585286 5946443575 10.02.202328585286 5946443699 10.02.202328585286 5946443705 20.02.202328587403 5946586447 7045127883461 5946789505 08.01.202328576858 5946853727 08.01.202328576858 5946853728 22028585286 5946954412 7069228428832 5947154833 7069228428832 5947154833 7069228428832 5947154833 7069228428832 5947154833 7069428428832 5947155105 7069428428832 5947155105 7069428428832 5947155105 7069428428832 5947155105 7070028428832 5947155230 7070028428832 5947155230 7070028428832 5947155230 7070028428832 5947155230 DOM2023240146128428832 5947256693 DOM2023240146128428832 5947256693 9128482429 5947289137 9128482429 5947289137 9128482429 5947289137 9128482429 5947289137 9128482429 5947289137 9128482429 5947289137 9128482429 5947289137 9128482429 5947289137 9128482429 5947289137 9128482429 5947289137 9128482429 5947289137 9128482429 5947289137 9128482429 5947289137 9128482429 5947289137 9128482429 5947289137 9128482429 5947289137 9128482429 5947289137 9128482429 5947289137 9128482429 5947289137 9128482429 5947289137 9228482429 5947289437 9228482429 5947289437 9228482429 5947289437 9228482429 5947289437 9228482429 5947289437 9228482429 5947289437 9228482429 5947289437 9228482429 5947289437 9228482429 5947289437 9228482429 5947289437 9528482429 5947291344 9528482429 5947291344 9528482429 5947291344 9528482429 5947291344 9528482429 5947291344 9528482429 5947291344 9528482429 5947291344 9528482429 5947291344 9528482429 5947291344 9528482429 5947291344 9528482429 5947291344 9528482429 5947291344 9428482429 5947291348 9428482429 5947291348 9428482429 5947291348 9428482429 5947291348 9428482429 5947291348 9428482429 5947291348 9428482429 5947291348 9428482429 5947291348 9428482429 5947291348 9428482429 5947291348 10028482429 5947291431 10028482429 5947291431 10028482429 5947291431 10028482429 5947291431 9728482429 5947291434 9728482429 5947291434 9728482429 5947291434 9728482429 5947291434 9728482429 5947291434 9728482429 5947291434 9928482429 5947291437 9928482429 5947291437 9928482429 5947291437 9928482429 5947291437 9928482429 5947291437 9928482429 5947291437 9828482429 5947291510 9828482429 5947291510 9828482429 5947291510 9828482429 5947291510 9828482429 5947291510 9828482429 5947291510 9828482429 5947291510 9828482429 5947291510 9828482429 5947291510 9828482429 5947291510 10128482429 5947402859 10128482429 5947402859 10228482429 5947402866 10228482429 5947402866 DOM2023240150228428832 5947403044 DOM2023240150228428832 5947403044 DOM2023240150428428832 5947403045 DOM2023240150428428832 5947403045 DOM2023240150428428832 5947403045 DOM2023240150428428832 5947403045 DOM2023240153528428832 5947403048 DOM2023240153528428832 5947403048 DOM2023240150428428832 5947403105 DOM2023240150428428832 5947403105 DOM2023240148628428832 5947403129 DOM2023240148628428832 5947403129 DOM2023240148728428832 5947403191 DOM2023240148728428832 5947403191 DOM2023240148828428832 5947403192 DOM2023240148828428832 5947403192 DOM2023240149228428832 5947403195 DOM2023240149228428832 5947403195 DOM2023240153128428832 5947403345 DOM2023240153128428832 5947403345 DOM2023240153328428832 5947403346 DOM2023240153328428832 5947403346 DOM2023240169828428832 5947403482 DOM2023240169828428832 5947403482 DOM2023240153228428832 5947403485 DOM2023240153228428832 5947403485 DOM2023240153428428832 5947403489 DOM2023240153428428832 5947403489 DOM2023240147828428832 5947405163 DOM2023240147828428832 5947405163 DOM2023240148028428832 5947405165 DOM2023240148028428832 5947405165 DOM2023240147928428832 5947405222 DOM2023240147928428832 5947405222 DOM2023240148128428832 5947405225 DOM2023240148128428832 5947405225 DOM2023240170628428832 5947405226 DOM2023240170628428832 5947405226 DOM2023240151928428832 5947405487 DOM2023240151928428832 5947405487 DOM2023240153728428832 5947405489 DOM2023240153728428832 5947405489 DOM2023240153828428832 5947405682 DOM2023240153828428832 5947405682 DOM2023240154128428832 5947405683 DOM2023240154128428832 5947405683 DOM2023240154328428832 5947405684 DOM2023240154328428832 5947405684 DOM2023240169928428832 5947405685 DOM2023240169928428832 5947405685 DOM2023240154228428832 5947405686 DOM2023240154228428832 5947405686 DOM2023240154428428832 5947405688 DOM2023240154428428832 5947405688 DOM2023240169928428832 5947405689 DOM2023240169928428832 5947405689 SEPL/202/23-2428601947 5947472159 SEPL/202/23-2428601947 5947472159 SEPL/203/23-2428601947 5947472322 SEPL/203/23-2428601947 5947472322 SEPL/204/23-2428601947 5947487589 SEPL/204/23-2428601947 5947487589 SEPL/200/23-2428601947 5947518405 SEPL/200/23-2428601947 5947518405 SEPL/201/23-2428601947 5947518407 SEPL/201/23-2428601947 5947518407 SEPL/205/23-2428601947 5947540367 SEPL/205/23-2428601947 5947540367 SEPL/206/23-2428601947 5947540369 SEPL/206/23-2428601947 5947540369 INV/04328684753 5947701395 INV/04328684753 5947701395 INV/04328684753 5947701395 INV/04328684753 5947701395 INV/04328684753 5947701395 INV/04328684753 5947701395 INV/04328684753 5947701395 INV/04328684753 5947701395 INV/04328684753 5947701395 1J/5/50069/23-2429112976 5947760402 1J/5/50069/23-2429112976 5947760402 1J/5/50068/23-2429112976 5947760403 1J/5/50068/23-2429112976 5947760403 DOM2023240047528428832 5947764671 DOM2023240047528428832 5947764671 DOM2023240047528428832 5947764671 DOM2023240047528428832 5947764671 DOM2023240047528428832 5947764671 DOM2023240047528428832 5947764671 DOM2023240047528428832 5947764671 DOM2023240047528428832 5947764671 DOM2023240150328428832 5947764675 DOM2023240150328428832 5947764675 DOM2023240150328428832 5947764675 DOM2023240150328428832 5947764675 DOM2023240047528428832 5947820731 DOM2023240047528428832 5947820731 DOM2023240047528428832 5947820731 DOM2023240047528428832 5947820731 DOM2023240047528428832 5947820731 DOM2023240047528428832 5947820731 1J/5/50081/23-2429112976 5947878329 1J/5/50081/23-2429112976 5947878329 1J/5/50081/23-2429112976 5947878329 1J/5/50082/23-2429112976 5947879253 1J/5/50082/23-2429112976 5947879253 1J/5/50082/23-2429112976 5947879253 09228482429 09228482429 AS/IOCL/23-24/0828914923 09228482429 #N/A 09228482429 #N/A 09228482429 #N/A 09228482429 #N/A DOM2023240047528428832 5947764671 09228482429 #N/A 09228482429 #N/A - BA_Pete2 years agoSuper User
Ok, let's break this down.
You have [Unique Key] which has a value in every row.
You have [Ref Key 3] which has some missing values.
You want to fill in the blanks in [Ref Key 3] using another column within the same table.
What is the name of the column that you want to get the values from to fill in the blank [Ref Key 3] values?
Assume that I have no idea what VLOOKUP does or how it works.
Pete
- KuntalSingh2 years agoHelper V
To get the values from Ref key 3, by looking up the unique key in the column. In other words to find the ref key 3 that matches with the unique key of the blank ref key 3
- BA_Pete2 years agoSuper User
Ah, ok. I think I understand now.
How do you want to assign a [Ref 3] value to a blank space when a single [Unique Key] can have multiple [Ref 3] values? Which [Ref 3] value do you want to put in the blank?
Pete