Expand the value for all similar column above or below in power query
I have a table as follows.
|---------------------|---------------------|
| Name | VEC |
|---------------------|---------------------|
| A | null |
|---------------------|---------------------|
| A | null |
|---------------------|---------------------|
| B | null |
|---------------------|---------------------|
| B | null |
|---------------------|---------------------|
| B_1 | 54 |
|---------------------|---------------------|
| D | null |
|---------------------|---------------------|
| D_1 | null |
|---------------------|---------------------|
| E | null |
|---------------------|---------------------|
| E_1 | 454 |
|---------------------|---------------------|
| E_2 | 454444 |
|---------------------|---------------------|
I am trying to produce the following result:
|---------------------|---------------------|---------------------|
| Name | VEC | Costum |
|---------------------|---------------------|---------------------|
| A | null | REF |
|---------------------|---------------------|---------------------|
| A | null | REF |
|---------------------|---------------------|---------------------|
| B | null | REF |
|---------------------|---------------------|---------------------|
| B | null | RUP |
|---------------------|---------------------|---------------------|
| B_1 | 54 | RUP |
|---------------------|---------------------|---------------------|
| D | null | REF |
|---------------------|---------------------|---------------------|
| D_1 | null | REF |
|---------------------|---------------------|---------------------|
| E | null | RUP |
|---------------------|---------------------|---------------------|
| E_1 | 454 | RUP |
|---------------------|---------------------|---------------------|
| E_2 | 454444 | RUP |
|---------------------|---------------------|---------------------|
In some cases, the letter has value in "VEC" this is thus easily solved with the conditional columns, which check "VEC" and is done.
However, in some cases, the letters repeat themselves such as B followed by "_". In this case the "_1" (or "_2", "_3"...) will be the only one with a value.
Here the conditional column does not work, because I would need the first occurence of the letter, i.e just the "A" or "B" to get a row with REF or RUP in the "costum" column.
#"Promoted Headers" = Table.PromoteHeaders(Table1_Sheet, [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Name", type text}, {"VEC", Int64.Type}}),
#"Added Conditional Column" = Table.AddColumn(#"Changed Type", "Custom", each if [VEC] = null then "REF" else "RUP")
in
#"Added Conditional Column"
The way you have gained your required output, is fine. I am just adding one more option here using Power Query below
Step-1 Add a Custom Column to your base table as below-
if Text.PositionOf([Name],"_") = -1
then [Name]
else
Text.Start([Name],Text.PositionOf([Name],"_"))
Here is the output-
Step-2: Now duplicate your base table and apply group by on column Custom and SUM on column VEC. You can right click on the Custom column and select Group BY option from the list and configure as below-
Step-3: Add a custom column as below after applying the Group BY and the final result will be as below-
if [sum] = null then "REF" else "RUP"
Step-4: Now Merge your base table with new table using Custom column and after expanding and keeping the colum custom.1 you will have this below final output-
