Reverse fill down (Unfill) in Power Query

Рет қаралды 2,478

2 жыл бұрын

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/academy/powerque...
#powerquery #powerbi #etltools #data #dataanalysis

Пікірлер: 14

@TheMoh148 2 жыл бұрын

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

@OsamaHassanin 2 жыл бұрын

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

@zackcarter3634 2 жыл бұрын

Great and easy tutorial to follow

@kebincui 7 ай бұрын

nice tutorial.Thanks

@navedsaiyed9881 Жыл бұрын

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

@melissa3558 2 жыл бұрын

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 2 жыл бұрын

Hi Melissa, Great tip! Thanks for sharing

@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)

@pablomirandasouza6965 2 жыл бұрын

Amazing! Thank you!

@GosCee 2 жыл бұрын

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

@Bhavik_Khatri Жыл бұрын

Nice vid tutorial

@warrenanderson412 Жыл бұрын

Nice

@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"} )

@krishnamanjunatha6183 2 жыл бұрын

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🙂