Duplicate rows based on condition

christiane.latte

christiane.latte

New member
Joined
Feb 27, 2018
Messages
14
Reaction score
0
Points
0
Excel Version(s)
2016
I would like to duplicate a row if a certain criteria is matched in a column:

Row Criteria Value
1 OK 100
2 Not OK 200
3 Not OK 300

So for the above I want to have row 1 duplicated.

On top of that I would like to have 30% of the Value remaining in row and 70% in the new row. Is that possible?
 
The thing I like about programming is that anything is possible; it's just how hard you have to work to get there! Here's a brute force approach

1. Pull in your data, let's call it query A
2. Reference query A to create query B, filter for OK in Criteria column, transform Value column by multiplying by 0.3
3. Reference query A to create query C, filter for OK in Criteria column, transform Value column by multiplying by 0.7
4. Reference query A to create query D, filter for Not OK in Criteria column
5. Append queries B,C,D

Norm
 
Here's what I did....

Create an Excel Table named Table1 for your data:
Code: 
Status       Value
OK           100
Not OK       200
Not OK       300
OK          1000
Created a second Excel Table named tblFactors containing this data:
Code: 
Status       Factor
OK           0.3
OK           0.7
Not OK       1.0
I connected both tables to Power Query

Then....I opened the Table1 Query and Merged Queries as New
This is the M-Code:
Code: 
let
    Source                 = Table.NestedJoin(Table1,{"Status"},tblFactors,{"Status"},"tblFactors",JoinKind.LeftOuter),
    #"Expanded tblFactors" = Table.ExpandTableColumn(Source, "tblFactors", {"Factor"}, {"Factor"}),
    #"Added Custom"        = Table.AddColumn(#"Expanded tblFactors", "NewValue", each [Value]*[Factor], type number)
in
    #"Added Custom"

These are the results:
Code: 
Status   Value     Factor   NewValue
OK       100       0.3       30
OK       100       0.7       70
OK       1000      0.3      300
OK       1000      0.7      700
Not OK    200      1        200
Not OK    300      1        300
Is that something you can work with?
 
An alternative solution, but with a bit of M-code.
Code: 
let
    Source = Excel.CurrentWorkbook(){[Name="Tabela1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Row", Int64.Type}, {"Criteria", type text}, {"Value", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "New Value", each if [Criteria] = "OK" then {[Value]*0.3, [Value]*0.7} else {[Value]}),
    #"Expanded {0}" = Table.ExpandListColumn(#"Added Custom", "New Value")
in
    #"Expanded {0}"

See attachment.
Reagrds :)
 

Attachments

  • PQ_KenForum.xlsx
    17.5 KB · Views: 23
Thanks, Ron and Bill, I found your answers very instructive and trust that the OP does too.
 
You must log in or register to reply here.
Back
Top