Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
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
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,
Happy to help!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
101 | |
52 | |
21 | |
12 | |
11 |