Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.