Populate Column if ID matches, then poulate all other rows with same ID

[Anonymous]

Hi,

Trying to find a clever way to populate a column that contain many blanks. Each ID are connected to many accounts, but my column (new merged column) only has values for a few different accounts. I would like to populate all other rows with that ID with the same value as the row that is not blank in my new merged column.

Below is an example how how I imagine this column to be

entry_no	doc_num	account	(new merged column)	New column
1	11	3040		url - 3
2	15	3023		urld -5
3	15	3040		urld -5
4	16	1234	url - 1	url - 1
5	19	3040		url - 4
6	20	1234	url - 2	url - 2
7	11	1234	url - 3	url - 3
8	15	1234	urld -5	urld -5
9	16	3040		url - 1
10	19	1234	url - 4	url - 4
11	20	3040		url - 2

Any good suggestions on how to make this happen?

Thanks!

[Smauro]

Hey @Anonymous,

From what I understand, you'd like to fill the empties with the unique value of [doc_num]->[(new merge column)] if there is any.
I've renamed (new merge column) to mrg.

This should work:

    NewTable = Table.SelectRows(Table.Group(Table.SelectRows(Table.SelectColumns(PreviousStep, {"doc_num", "mrg"}), each [mrg] <> null and Text.Trim([mrg]) <> ""), {"doc_num"}, {{"mrg", each [mrg]{0}, type text}, {"c", each List.Count(List.Distinct([mrg])), Int64.Type}}), each [c]=1),
    MergeBack = Table.TransformColumns(Table.NestedJoin(PreviousStep, {"doc_num"}, NewTable, {"doc_num"}, "new mrg", JoinKind.LeftOuter), {"new mrg", (curr) => curr[mrg]{0}}),
    AddResult = Table.AddColumn(MergeBack, "Result", each if [mrg] <> null and Text.Trim([mrg]) <> "" then [mrg] else [new mrg], type text)
in
    AddResult

PreviousStep is your previous step.

NewTable is taking columns [doc_num] & [mrg], keeping non empty, grouping to see if they are unique and keeping the unique values only.

MergeBack merges the NewTable back to our current table, taking the unique [mrg] value based on [doc_num].

AddResult sees if there was already a value and keeps this one (this is done in case [doc_num]<->[mrg] wasn't unique) and if not it gets the value from our [new mrg] column.

Cheers

smauro

[ibarrau]

Hi there, i don't quite understand the specific column to replace with. That's why the suggested formula will have a different name. Pay attention:

= Table.ReplaceValue(#"Last Step","", each _[ColumnName],Replacer.ReplaceValue,{"(new merged column)"})

The second argument after "Last Step" is the value to replace. Check if you have null, "space" or just "" (there is no code to talk about blanks in power query). The _[ColumnName] is the column that will be taken to complete (new merged column) blank values.

Hope this helps,

Regards,