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
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
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
Helpful resources
Join our Fabric User Panel
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Power BI Monthly Update - June 2025
Check out the June 2025 Power BI update to learn about new features.
Fabric Community Update - June 2025
Find out what's new and trending in the Fabric community.
