PowerQuery - fill value in column based on condition

[JakubWiniarczk]

Hi Everyone,

I've some problem with creating PowerQuery formula for my data like below:

Indeks	Description	Value
1	Opis1	Test
2	Opis2
3	Opis3
4	Opis4
5	Opis4	Test2
6	Opis5
7	Opis6
8	Opis7	Test3
9	Opis7
10	Opis8
11	Opis9	Test4
12	Opis10

In my excel file, I've got filled column 'Value' only for the first row in the group. I need to create a new column for sheet-like below:

Indeks	Description	Value	New Column
1	Opis1	Test	Test
2	Opis2		Test
3	Opis3		Test
4	Opis4		Test
5	Opis4	Test2	Test2
6	Opis5		Test2
7	Opis6		Test2
8	Opis7	Test3	Test3
9	Opis7		Test3
10	Opis8		Test3
11	Opis9	Test4	Test4
12	Opis10		Test4

Row for each group maybe is different for each import.

Any ideas? 

[Jimmy801 · Accepted solution]

Hello @JakubWiniarczk 

I've prepared a solution for you. This involves replacing empty cells with null and then apply a filldown.

Here the code

let
	Source = #table
	(
		{"Indeks","Description","Value"},
		{
			{"1","Opis1","Test"},	{"2","Opis2",""},	{"3","Opis3",""},	{"4","Opis4",""},	{"5","Opis4","Test2"},	{"6","Opis5",""},	{"7","Opis6",""},	{"8","Opis7","Test3"},	
			{"9","Opis7",""},	{"10","Opis8",""},	{"11","Opis9","Test4"},	{"12","Opis10",""}
		}
	),
    ReplaceEmptywithNull = Table.ReplaceValue(Source,"",null,Replacer.ReplaceValue,{"Value"}),
    FillDown = Table.FillDown(ReplaceEmptywithNull,{"Value"})
in
	FillDown

Copy paste this code to the advanced editor in a new blank query to see how the solution works. If this solution fits your need, copy and past a part of it and implement it in your query

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

[Jimmy801]

Hello @JakubWiniarczk 

have you been able to solve the problem with the replies given?

If so, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

All the best

Jimmy

[v-frfei-msft · Accepted solution]

Hi @JakubWiniarczk ,

In power query, we can fill the column down as below.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Tc69CoAwDATgd8ncwTbp31O4uJWODm5CfX+k8SKdDj5y4VojT472+xozj3M81F2jAJypwAA2EIAYxAXmn6CaoNHOMiAZFEBGj1Xrogp+g5RfbHlFUz627dqg3l8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Indeks = _t, Description = _t, Value = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Indeks", Int64.Type}, {"Description", type text}, {"Value", type text}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type","",null,Replacer.ReplaceValue,{"Value"}),
    #"Filled Down" = Table.FillDown(#"Replaced Value",{"Value"})
in
    #"Filled Down"

Alternatively, We can achieve that by DAX.

Column = 
VAR a = 'Table (2)'[Indeks]
VAR ind =
    CALCULATE (
        MAX ( 'Table (2)'[Indeks] ),
        FILTER (
            'Table (2)',
            'Table (2)'[Value] <> BLANK ()
                && 'Table (2)'[Indeks] <= a
        )
    )
RETURN
    CALCULATE (
        MAX ( 'Table (2)'[Value] ),
        FILTER ( 'Table (2)', 'Table (2)'[Indeks] = ind )
    )

Pbix as attached.

[Jimmy801 · Accepted solution]

Hello @JakubWiniarczk 

I've prepared a solution for you. This involves replacing empty cells with null and then apply a filldown.

Here the code

let
	Source = #table
	(
		{"Indeks","Description","Value"},
		{
			{"1","Opis1","Test"},	{"2","Opis2",""},	{"3","Opis3",""},	{"4","Opis4",""},	{"5","Opis4","Test2"},	{"6","Opis5",""},	{"7","Opis6",""},	{"8","Opis7","Test3"},	
			{"9","Opis7",""},	{"10","Opis8",""},	{"11","Opis9","Test4"},	{"12","Opis10",""}
		}
	),
    ReplaceEmptywithNull = Table.ReplaceValue(Source,"",null,Replacer.ReplaceValue,{"Value"}),
    FillDown = Table.FillDown(ReplaceEmptywithNull,{"Value"})
in
	FillDown

Copy paste this code to the advanced editor in a new blank query to see how the solution works. If this solution fits your need, copy and past a part of it and implement it in your query

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy