Power Query Excel - update rows based on other rows value

Question

I am importing some data from SQL Server to Excel and I need to change the values of one column based on the values of other rows from the same column.

This is the initial table:

What I want to achieve is: for each Visit Date, Rep, Chestionar and Customer:

to populate all the rows from "PLANOGRAME Type 1" with the value from the same column where Brand = 'Planograme Type 1'

to populate all the rows from "PLANOGRAME Type 2" with the value from the same column where Brand = 'Planograme Type 2'

to populate all the rows from "POZITIONARE" with the value from the same column where Brand is not 'Planograme Type 1' or 'Planograme Type 2'

So the table should look like this in the end:

I managed to do this on SQL side, but the query it's too heavy and it doesn't run, so I wanna do this from Power Query. Is there any way of doing it?

Answer 1

Making some assumptions based on my comments to your question:

Assumptions

• Unlike your example, but congruent with your description, you DO want the POZITIONAIRE column populated with all the non-Plano type Brands
• If there are multiple items that the rules would put into one of the three columns, they will be concatenated with commas

you can try the following: (any of the assumptions can be changed with different coding)

M Code
Edited to allow for multiple matches in the three columns

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{
        {"Visit Date", type date}, {"Rep", type text}, {"Chestionar", type text}, 
        {"ID", Int64.Type}, {"Customer", type text}, {"PLANOGRAME Type 1", type text}, 
        {"PLANOGRAME Type 2", type text}, {"POZITIONARE", type text}, {"Brand", type text}}),

    //Group by Date, Rep, Chestionar and Customer, then extract what's needed for the three columns
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Visit Date","Rep","Chestionar","Customer"}, {
        {"All", each _, type table [Visit Date=nullable date, Rep=nullable text, Chestionar=nullable text, ID=nullable number, Customer=nullable text, PLANOGRAME Type 1=nullable text, PLANOGRAME Type 2=nullable text, POSITIONARE=nullable text, Brand=nullable text]},
        {"PLANOGRAME Type 1", (t)=> 
                Text.Combine(
                    Table.SelectRows(t, each [Brand] = "Planograme Type 1")[PLANOGRAME Type 1],", ")
                    , type text},
        {"PLANOGRAME Type 2", (t)=> 
                Text.Combine(
                Table.SelectRows(t, each [Brand] = "Planograme Type 2")[PLANOGRAME Type 2],", ")
                    , type text},
        {"POZITIONARE", (t)=>
                Text.Combine(
                    Table.SelectRows(t, 
                        each List.Contains({"Planograme Type 1","Planograme Type 2"},[Brand])= false)[POZITIONARE],", ")
                        , type text 
                    }
        }),

//get original column name order
colNameOrder = Table.ColumnNames(Source),

//expand the table, and put columns back into original order
    #"Expanded All" = Table.ExpandTableColumn(#"Grouped Rows", "All", 
        {"ID", "Brand"}, {"ID", "Brand"}),
    reOrder = Table.ReorderColumns(#"Expanded All",colNameOrder)
in
    reOrder