none
Using Power Query to convert multiple rows into columns (quasi-pivot) RRS feed

  • Question

  • This is probably simple, yet I've been unable to find anything that references it exactly.

    I'm using an API to pull data into power query - for the most part, this creates distinct columns for each value.  However, a portion of the data comes through as a list with associated values in 2 columns.  I'm having issues separating the data so that it will pull over as distinct columns with data beneath it.

    For example, the data comes over like this (note: column2/3 come over as a list that currently I expand):

    Column1      Column2      Column3

    Job1            BillRate         $100

    Job1            ProjectName  LivingRoom

    Job2            BillRate         $75

    Job2            ProjectName   DiningRoom

    I'm trying to shape it all onto one row per job to look like this:

    Column1     Column2         Column3

    Job1           LivingRoom      $75

    Job2           DiningRoom     $100

    Any ideas out there?

    Friday, April 17, 2015 1:51 AM

Answers

  • Hi Jimmy,

    Assuming your data is in Table1, here is the Power Query expression that you can use:

    let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        #"Pivoted Column" = Table.Pivot(Source, List.Distinct(Source[#"Column 2"]), "Column 2", "Column 3"),
        #"Renamed Columns" = Table.RenameColumns(#"Pivoted Column",{{"Column 1", "Job"}})
    in
        #"Renamed Columns"

    You can open a blank query and paste the expression above inside Query Editor dialog in View-->Advanced Editor.

    You can also perform the following steps manually:

    1. Use "From Table" to import the data from the worksheet into Query Editor.
    2. Select Column1
    3. Click Transform-->Pivot Columns
    4. In the Pivot Columns dialog, select Column 3 as "Value Column".
    5. Expand the Advanced Options.
    6. In the drop down "Aggregate Value Function" select "Don't Aggregate".
    7. Click OK.
    8. The Editor will should you the desired table format. You can also rename Column 1 to "Job" as I did in the expression above.

    Hope it helps.

    Gil


    Friday, April 17, 2015 8:42 AM