Reverse fill down (Unfill) in Power Query

You've probably already met with fill-down operation in Power Query, but this time we show you 2 techniques of unfill or reverse fill operation!!
Although the first technique is faster, the second one hides complexity which could help you in more advanced scenarios.
Check out both and tell us how you like it! See you soon with more videos!
👉To learn more about Power Query, check out our Power Query fundamentals course!
exceed.hr/en/a...
#powerquery #powerbi #etltools #data #dataanalysis

[@janwillemvanholst]

let
Source = YourData,
_distinct=List.Distinct(Source[Name]),
_count = List.Count(_distinct) - 1,
_list =
List.Accumulate(
{0.._count},
{},
(s,c)=> s & {_distinct{c}} & List.Repeat({null}, List.Count(List.Select(Source[Name], each _ = _distinct{c}))-1 )
)
in
Table.FromColumns(
{_list} & {Source[Value]},
{"Name", "Value"}
)

[@OsamaHassanin]

Thanks for the two methods which are helpfull.
another method
we can add to the table 2 indexes columns one start with 0 and other with one
Merge the same table to current linking index 0 to index 1 and expand the name
Sort with index 0
Custom column if name = expanded name then null else name.
This will give the same results

[@TheMoh148]

Hi, many thanks.
For better performance I will propose the steps below:
after Changed Type step
Step 1:
Add column index from 0
Step 2:
Creat and buffer a list of previous Names
PrvName = List.Buffer( {null} & #“Added Index”[Name])
Step 3:
Add a new column with previous name
Table.AddColumn( #”Added Index”, “ previous name “, each PrvName{[Index]})
Continue the remaining steps.
Regards | Mohammed from Algeria

[@victor_wang_1]

After adding the index column from 0:
= Table.AddColumn(#"Added Index", "Custom", each if List.PositionOf(#"Added Index"[Name], [Name]) = [Index] then [Name] else null)

[@zackcarter3634]

Great and easy tutorial to follow

[@melissa3558]

Fun problem Kresimir
If you want to be able to expand the Index column as well, you can add the Index field to the column type setting record OR remove that record completely from the Grouped Rows step, once done, it will show up with the other columns 😉 All the best.

[@ExceedLearning]

Hi Melissa,
Great tip! Thanks for sharing

[@navedsaiyed9881]

Few Minutes ago i was looking to resolve this with my query & when tried to search the solution found it on top.

[@kebincui]

nice tutorial.Thanks

[@krishnamanjunatha6183]

Row 1
Row 2
Row 3
Row 4 Description Qnty Amount
Row 5 null null TaxableValue TaxAmount
Row 6 A 1 100 10
Row 7 B 5 null null
Row 8 C 2 20 2
Fill down should happen only for Row 4 & Row 5 but not for all the rows. Thanks for your help🙂

[@pablomirandasouza6965]

Amazing! Thank you!

[@Bhavik_Khatri]

Nice vid tutorial

[@GosCee]

Thank you, sir. This is exactly what I needed. Great!

[@warrenanderson412]

Nice