Forum Discussion

callmerain's avatar
callmerain
Frequent Visitor
4 years ago
Solved

Power Query Editor - Moving data from one row to another only in certain columns

Hi, I'm trying to figure out if there is any way to move data from 1 row to another (maybe based on an IF statement?) in Power Query Editor.   I have data that I am pulling in from excel file that...
  • callmerain's avatar
    4 years ago

    Solved it!

     

    Going to leave my solution here for posterity:

     

    First, I duplicated my import, to have something to mold.

    I used Keep Rows > Keep a range of rows to pull out only the 3 rows that I wanted to deal with (in my Sample case, it would be 3, 4, and 5)

    Then I transposed them, so that I was working with 3 columns.

    I added a custom column with an IF statement to check the first column for / (which only my date columns would have) and to either pull the dates or the main header info from the other column:

    each if [Column1] = null then [Column3] else if Text.Contains([Column1], "/") then [Column1] else [Column3])

    That gave me the solution to my main problem.  To handle that secondary word split, I created another Custom Column where if the split row cell was null, it would pull from my newly created column, otherwise it would concatonate the to row cells:

    each if [Column2] = null then [Headers] else Text.From([Column2]) & " " & Text.From([Column3])

    Then I removed all other columns, and transposed the Column back to a Row, and Appended my original data set onto the new row.  Promoted the top row to Headers and removed all of the unneeded rows between that and the beginning of the data