none
Create new row referring existing column in table RRS feed

  • Question

  • Hi, 

    I'm just new to this forum and striving to achieve as below in Power Query only which fit to my requirement:

    Input

    Column1

     

    Column2

     

    Column3

     

    Column4

    1

    XYZ

    10

    6

    2

    ABC

    13

    0

    3

    DEF

    65

    12

    4

    GHG

    100

    15

    5

    KLP

    32

    0

    6

    MKO

    25

    12

     

    Output

    Column1

    Column2

    Column3

    Column4

    1

    XYZ

    10

    0

    1

    XYZ

    0

    6

    2

    ABC

    13

    0

    3

    DEF

    65

    0

    3

    DEF

    0

    12

    4

    GHG

    100

    0

    4

    GHG

    0

    15

    5

    KLP

    32

    0

    6

    MKO

    25

    0

    6

    MKO

    0

    12

     

    If the value in the Column-4 is greater then 0 then,

        1. new row should copy paste referring above record

        2. copied row should not copy the data containing in column 3

    Would appreciate, if any can help on Power Query to achieve this.

    Appreciate your kind help.

    Regards,

    Arjun



    • Edited by Arjun926 Wednesday, June 20, 2018 11:10 AM
    Wednesday, June 20, 2018 11:07 AM

Answers

  • Always interesting to learn from your different solutions.

    Here is another approach, maybe more straight: just creating a new column with the results, and expanding.

    let
        Source = SomeTable,
        AddTable = Table.AddColumn(#"Source", "Results", each 
                if [Column4]>0
                then #table({"Column3", "Column4"}, {{[Column3],0}, {0, [Column4]}}) 
                else #table({"Column3", "Column4"}, {{[Column3], [Column4]}}), 
                type table [Column3=Int64.Type, Column4=Int64.Type] 
        ) [[Column1],[Column2],[Results]],
        ExpandCol = Table.ExpandTableColumn(#"AddTable", "Results", {"Column3", "Column4"}, {"Column3", "Column4"})
    in #"ExpandCol"




    Thursday, June 21, 2018 8:18 AM

All replies

  • Hi Arjun, 

    It's good to see an example input and output (it's very useful for us). 

    let
        Source = SomeTable,
        List = Table.ToRecords(Source),
        func = each if _[Column4] > 0 then {Record.TransformFields(_,{"Column4", each 0})
                                           ,Record.TransformFields(_,{"Column3", each 0})} else {_},
        ListTrans = List.Transform(List,func),
        Combine = List.Combine(ListTrans),
        Table = Table.FromRecords(Combine),
        TableWithTypes = Value.ReplaceType(Table,Value.Type(Source))
    in
        TableWithTypes


    • Edited by CamWally Wednesday, June 20, 2018 5:10 PM
    Wednesday, June 20, 2018 3:30 PM
  • Another option would be to do the required transformations through the Power Query UI. In the following example, all of the steps after the source step (except the last step) were created in the UI. The steps were renamed in the UI to avoid the ugly default quoted names like (#"Unpivoted Columns", #"Filtered Rows", etc.)

    let
        Source = SomeTable,
        UnpivotedColumns = Table.UnpivotOtherColumns(Source, {"Column1", "Column2"}, "Attribute", "Value"),
        FilteredRows = Table.SelectRows(UnpivotedColumns, each ([Value] <> 0)),
        AddedIndex = Table.AddIndexColumn(FilteredRows, "Index", 0, 1),
        PivotedColumn = Table.Pivot(AddedIndex, List.Distinct(AddedIndex[Attribute]), "Attribute", "Value"),
        ReplacedValue = Table.ReplaceValue(PivotedColumn,null,0,Replacer.ReplaceValue,{"Column3", "Column4"}),
        RemovedIndex = Table.RemoveColumns(ReplacedValue,{"Index"}),
        RestoredColumnTypes = Value.ReplaceType(RemovedIndex, Value.Type(Source))
    in
        RestoredColumnTypes
    Wednesday, June 20, 2018 7:35 PM
  • Hi Colin,

    There is a problem with your example. If records Column3 and Column4 are both 0 then that record will disappear.



    • Edited by CamWally Wednesday, June 20, 2018 9:44 PM
    Wednesday, June 20, 2018 8:36 PM
  • There is a problem with your example. If records Column3 and Column4 are both 0 then that record will disappear.

    Indeed! The assumption is that, in the source,  there is no record if both column values are zero (alternatively, there is always a non-zero value in at least one column). However, I have no idea whether those assumptions are true or not.

    There is also an interesting question about the logic, i.e. :

    1) If two columns have non-zero values, the output generates two rows for each row in the source.
    2) If one column has a non-zero value, the output generates one row for each row in the source.
    3) If zero columns have non-zero values, he output generates ??? for each row in the source.


    Wednesday, June 20, 2018 11:16 PM
  • Always interesting to learn from your different solutions.

    Here is another approach, maybe more straight: just creating a new column with the results, and expanding.

    let
        Source = SomeTable,
        AddTable = Table.AddColumn(#"Source", "Results", each 
                if [Column4]>0
                then #table({"Column3", "Column4"}, {{[Column3],0}, {0, [Column4]}}) 
                else #table({"Column3", "Column4"}, {{[Column3], [Column4]}}), 
                type table [Column3=Int64.Type, Column4=Int64.Type] 
        ) [[Column1],[Column2],[Results]],
        ExpandCol = Table.ExpandTableColumn(#"AddTable", "Results", {"Column3", "Column4"}, {"Column3", "Column4"})
    in #"ExpandCol"




    Thursday, June 21, 2018 8:18 AM
  • Agree with Maxim. Anthony's solution is the simplest for this problem. Well done!
    Friday, June 22, 2018 2:37 PM