Power Query Transform/Lookup Data From Rows To Columns

Question

I have a set of data ("My Data") shown below, how to shift the data from rows to columns in Power Query?

("My Preferred Answer") would be the final output.

My Data:

|  FruitName  |  Price   |  Quantity  |
|  ---------  |  -----   |  --------  |
|  Apple      |  1       |  1         |
|  Banana     |  2       |  1         |
|  Orange     |  3       |  1         |
|  Colour     |  *null*  |  *null*    |
|  Apple      |  Red     |  *null*    |
|  Banana     |  Yellow  |  *null*    |
|  Orange     |  Orange  |  *null*    |

My Preferred Answer:

|  FruitName  |  Price  |  Quantity  |  Colour  |
|  ---------  |  -----  |  --------  |  ------  |
|  Apple      |      1  |         1  |  Red     |
|  Banana     |      2  |         1  |  Yellow  |
|  Orange     |      3  |         1  |  Orange  |

Answer 1

Read the code comments to better understand the algorithm.

• Split the table at the "colour" line
• Join the colours with the inventory table

M Code

let

//Change Table name in next line to the actual table name in your workbook
    Source = Excel.CurrentWorkbook(){[Name="Table28"]}[Content],

//Separate table for Color and Inventory
    splitTable = Table.SplitAt(Source, List.PositionOf(Source[FruitName],"Colour")),

//Remove the row with the table split word "colour"
//Remove the Quantity column
//Rename the Price column
//Set the data types
    colourTable = Table.TransformColumnTypes(
        Table.RenameColumns(
            Table.RemoveColumns(
                Table.RemoveFirstN(splitTable{1},1),"Quantity"),{"Price","Colour"}),
                    {{"FruitName",type text},{"Colour",type text}}),

//Set the data types
    inventoryTable = Table.TransformColumnTypes(splitTable{0},{
        {"FruitName", type text},
        {"Price",Currency.Type},
        {"Quantity", Int64.Type}
        }),

//Join the colour column
    joined = Table.NestedJoin(inventoryTable,"FruitName",colourTable,"FruitName","Joined",JoinKind.LeftOuter),
    #"Expanded Joined" = Table.ExpandTableColumn(joined, "Joined", {"Colour"}, {"Colour"})

in
   #"Expanded Joined"