cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper V
Helper V

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

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_nodoc_numaccount(new merged column)New column
1113040 url - 3
2153023 urld -5
3153040 urld -5
4161234url - 1url - 1
5193040 url - 4
6201234url - 2url - 2
7111234url - 3url - 3
8151234urld -5urld -5
9163040 url - 1
10191234url - 4url - 4
11203040 url - 2

 

Any good suggestions on how to make this happen?

 

Thanks!

2 REPLIES 2
Solution Specialist
Solution Specialist

Hey @PBISea,

 

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




Feel free to connect with me:
LinkedIn

Super User II
Super User II

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,



If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Happy to help!

LaDataWeb Blog


Helpful resources

Announcements
secondImage

Happy New Year from Power BI

This is a must watch for a message from Power BI!

December Update

Check it Out!

Click here to read more about the December 2020 Updates!

Community Blog

Check it Out!

Click here to read the latest blog and learn more about contributing to the Power BI blog!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors