Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hello everybody,
I'm starting my journey with PowerQuery and I'm currently facing an issue that could be solved with your suuport.
I have a table containing various requests (column id). Some of these requests are linked to a parent request (column parent_id). See below:
id - parent_id - DATA
1 1 A
2 1
3 5
4 1
5 5 B
My goal is to fill the empty cells of the DATA column with the values of the cells which are not empty and in relationship with the parent request.
Please find below the final table I'm looking for:
id - parent_id - DATA
1 1 A
2 1 A
3 5 B
4 1 A
5 5 B
Thanks in advance for your help.
Rémi
Hello,
It also works perfectly, thanks again 🙂 !
Yesterday I tried your first solution with a bigger db and I succeed so it's a very good news.
Rémi
Hello,
Thanks for your quick answer.
After some adjustments to fit with my source file, it works perfectly :).
Now I have to adapt it to a bigger db !
Thanks a lot for your support :)!
Rémi
Great! By I should have started with something simple. Like Table.Group and Table.FillUp (or FillDown). Try this:
let
Source = your_table,
#"Grouped Rows" =
Table.Group(
Source, "parent_id",
{{"rows", each Table.FillUp(Table.Sort(_, "DATA"), {"DATA"})}}),
#"Expanded rows" = Table.ExpandTableColumn(#"Grouped Rows", "rows", {"id", "DATA"})
in
#"Expanded rows"
Hello, RVIG
one of several options to solve this:
let
Source = your_table,
values = Table.SelectRows(Table.Distinct(Source[[parent_id], [DATA]]), each [DATA] <> null),
dict = Record.FromList(values[DATA], List.Transform(values[parent_id], Text.From)),
helper_column =
Table.AddColumn(
Source, "helper",
(x) =>
if x[DATA] = null
then Record.FieldOrDefault(dict, Text.From(x[parent_id]), null)
else x[DATA]
)
in
helper_column
then remove "DATA" column and rename "helper" as you want. Instead of exta (temporal) column one may choose to use Table.ReplaceValue (proved to be slower than other options) or a combination of Record.TransformFields and Table.TransformRows functions.