none
Adding if-condition to new column RRS feed

  • Question

  • Hi,

    I have a table with 4 columns. 

    The information varies for three columns and is linked to the first. Therfore the values of the first column appear more than once.

    Example:

    A B C D

    1 123 xy abc

    2 124 yz abd

    2 125 xz abe

    3      126 zz abf

    4 ......

    I would like to insert a new column with a function, checking if A2=A3 and if so, adding the information of the other three columns to that row. 

    Like this:

    A B C D E F G

    1    123   xy    abc

    2    124   yz   abd   125   xz   abe

    2    125   xz   abe    

    3    126  zz    abf

    4...

    Is this possible with power query?

    Thank you

    Monday, April 7, 2014 3:48 PM

Answers

  • Yes, that is clearer. I am unaware of an easy solution, but something like this should work:

    let
        Source = (table) =>
        let
            InsertedIndex = Table.AddIndexColumn(table,"Index"),
            ChangedType = Table.TransformColumnTypes(InsertedIndex,{{"A", type text}}),
            GetUniqueAValues = List.Accumulate(ChangedType[A], [], (state, current) => if Record.HasFields(state, current) then state else Record.AddField(state, current, [])),
            GetOtherRowValues = List.Accumulate(ChangedType[Index], GetUniqueAValues, (state, index) => 
                let
                    RecordKey = ChangedType[A]{index},
                    RecordLength = Record.FieldCount(Record.Field(state, RecordKey)),
                    RecordSubscript = Number.ToText(RecordLength),
                    UpdatedRecord = Record.TransformFields(state, {RecordKey, (oldRecord) => 
                       let
                          NewBField = Record.AddField(oldRecord, "B" & RecordSubscript, ChangedType[B]{index}),
                          NewCField = Record.AddField(NewBField, "C" & RecordSubscript, ChangedType[C]{index}),
                          NewDField = Record.AddField(NewCField, "D" & RecordSubscript, ChangedType[D]{index})
                       in
                          NewDField})
                in UpdatedRecord),
            ConvertedToTable = Record.ToTable(GetOtherRowValues),
            ColumnsToExpand = List.Accumulate(ConvertedToTable[Value], {}, (state, current) => if Record.FieldCount(current) > List.Count(state) then Record.FieldNames(current) else state),
            FinalTable = Table.ExpandRecordColumn(ConvertedToTable, "Value", ColumnsToExpand)
        in
            FinalTable
    in
        Source

    You can use this one your table by calling the function. For example, if the query above was called "FoldValuesIntoOneRow" , you can get your new table by making a new query which does "= FoldValuesIntoOneRow(MyTable)" . You will have to rename the columns to something you like, and if your columns are not named A/B/C/D, you'll have to change those values in the above query.

    The query works by:

    1) Adding an index column so we can easily loop through the rows

    2) Converting the type of values in A to a Text because Record.HasFields only works if the record field name is of type Text.

    3) Initializing a record containing all the values of A (e.g. 1, 2, 3). Each value refers to a blank record at this point.

    4) Combining the column values of B, C, and D into the appropriate record.  This is done by looping through the rows using the Index column from earlier. At each part in the loop we check how many times the value in A has been seen, and we generate a column name from that. We then get the record that belongs to the value in A and add the values in B, C, and D from the current row before adding it back.

    5) Converting the record to a table. The table will have one column containing all the values of A and another column which contains a record of the remaining columns.

    6) Finding out how many columns we will need to expand. We can do this by finding the record with the most fields, since each field will become a column. We get the list of names because it is needed for the last part.

    7) Expanding the record column. This will give us a table with only unique values for A and with columns containing all the B/C/D values that belonged to that value.

    For example, if our table looked like:

    A B C D
    1 123 xy abc
    1 456 yz abd
    1 789 xz abe
    2 126 xx abf
    2 127 zz abg
    3 128 zy abh

    Our new table will be:

    Name B0 C0 D0 B3 C3 D3 B6 C6 D6
    1 123 xy abc 456 yz abd 789 xz abe
    2 126 xx abf 127 zz abg   
    3 128 zy abh  

    Hope that helps.

    -Alejandro

    Tuesday, April 22, 2014 6:29 PM

All replies

  • A couple questions:

    1) Can the same value in the first column appear more than twice (e.g. 1, 2, 2, 2, 3)?

    2) If the same value appears in the column, are they always clumped together (like 1, 2, 2, 3 instead of 1, 2, 3, 2)?T

    Thanks.

    -Alejandro

    Tuesday, April 8, 2014 8:50 PM
  • 1. yes, this is possible. the value can appear once, up to 15 times or not at all

    2. since I sorted the column, the same values are set together

    Wednesday, April 9, 2014 7:47 AM
  • Another question: Do you want to add 3 columns for each duplicate? For example, if my original table is:

    A B C D

    1 123 xy abc

    1 124 yz abd

    125 xz abe

    2 126  zz abf

    should the table be

    A B C D E F G H I J

    1 123 xy abc 124 yz abd 125 xz abe

    1 124 yz abd

    125 xz abe

    2 126  zz abf

    or should it be

    A B C D E F G

    1 123 xy abc 124 yz abd

    1 124 yz abd 125 xz abe

    125 xz abe

    2 126  zz abf

    Thanks.

    Friday, April 11, 2014 1:18 AM
  • What I need is this:

    A B C D E F G H I J

    1 123  xy abc 124 yz abd 125 xz abe

    1 124  yz abd

    125  xz abe

    2 126  zz abf

    and then afterwards only the first row, which now has all the information.

    But since the first column may have value "1" more than three times, there might be more than three columns added.

    And I also think it is only possible to apply the rule for every row??

    Friday, April 11, 2014 7:46 AM
  • Hi, sorry for getting back a bit late. When you say "and then afterwards only the first row," do you mean that the end result should have a unique value for A? In other words, in the example above, should there only be one instance of the value 1 in column A (the one that has data in all of the columns)?

    Thanks.

    -Alejandro

    Thursday, April 17, 2014 11:13 PM
  • I'm not sure if I understand the question. 

    I need the complete data for each value in column A. The information should be added as additional columns and then only the top row for each value in A would be needed (since the other rows would only hold information that is already written in another row).

    Clearer? I think that is what you mean, right?

    Thanks for trying to help. Maybe there isn't an easy solution? ;-)

    Tuesday, April 22, 2014 4:44 PM
  • Yes, that is clearer. I am unaware of an easy solution, but something like this should work:

    let
        Source = (table) =>
        let
            InsertedIndex = Table.AddIndexColumn(table,"Index"),
            ChangedType = Table.TransformColumnTypes(InsertedIndex,{{"A", type text}}),
            GetUniqueAValues = List.Accumulate(ChangedType[A], [], (state, current) => if Record.HasFields(state, current) then state else Record.AddField(state, current, [])),
            GetOtherRowValues = List.Accumulate(ChangedType[Index], GetUniqueAValues, (state, index) => 
                let
                    RecordKey = ChangedType[A]{index},
                    RecordLength = Record.FieldCount(Record.Field(state, RecordKey)),
                    RecordSubscript = Number.ToText(RecordLength),
                    UpdatedRecord = Record.TransformFields(state, {RecordKey, (oldRecord) => 
                       let
                          NewBField = Record.AddField(oldRecord, "B" & RecordSubscript, ChangedType[B]{index}),
                          NewCField = Record.AddField(NewBField, "C" & RecordSubscript, ChangedType[C]{index}),
                          NewDField = Record.AddField(NewCField, "D" & RecordSubscript, ChangedType[D]{index})
                       in
                          NewDField})
                in UpdatedRecord),
            ConvertedToTable = Record.ToTable(GetOtherRowValues),
            ColumnsToExpand = List.Accumulate(ConvertedToTable[Value], {}, (state, current) => if Record.FieldCount(current) > List.Count(state) then Record.FieldNames(current) else state),
            FinalTable = Table.ExpandRecordColumn(ConvertedToTable, "Value", ColumnsToExpand)
        in
            FinalTable
    in
        Source

    You can use this one your table by calling the function. For example, if the query above was called "FoldValuesIntoOneRow" , you can get your new table by making a new query which does "= FoldValuesIntoOneRow(MyTable)" . You will have to rename the columns to something you like, and if your columns are not named A/B/C/D, you'll have to change those values in the above query.

    The query works by:

    1) Adding an index column so we can easily loop through the rows

    2) Converting the type of values in A to a Text because Record.HasFields only works if the record field name is of type Text.

    3) Initializing a record containing all the values of A (e.g. 1, 2, 3). Each value refers to a blank record at this point.

    4) Combining the column values of B, C, and D into the appropriate record.  This is done by looping through the rows using the Index column from earlier. At each part in the loop we check how many times the value in A has been seen, and we generate a column name from that. We then get the record that belongs to the value in A and add the values in B, C, and D from the current row before adding it back.

    5) Converting the record to a table. The table will have one column containing all the values of A and another column which contains a record of the remaining columns.

    6) Finding out how many columns we will need to expand. We can do this by finding the record with the most fields, since each field will become a column. We get the list of names because it is needed for the last part.

    7) Expanding the record column. This will give us a table with only unique values for A and with columns containing all the B/C/D values that belonged to that value.

    For example, if our table looked like:

    A B C D
    1 123 xy abc
    1 456 yz abd
    1 789 xz abe
    2 126 xx abf
    2 127 zz abg
    3 128 zy abh

    Our new table will be:

    Name B0 C0 D0 B3 C3 D3 B6 C6 D6
    1 123 xy abc 456 yz abd 789 xz abe
    2 126 xx abf 127 zz abg   
    3 128 zy abh  

    Hope that helps.

    -Alejandro

    Tuesday, April 22, 2014 6:29 PM