Split Data In Multiple Columns

Here is an example code that you can paste into the advanced editor of a blank query and work through the steps.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcjTy8VbSUTLSMwKSAcb+bo5A2sQUSEQY+7gDKVNjkAwIxepEK/kY+YDEzEzAqg3CgJQZVDFEtQlIdYSpQSSQMrcwAWvyNbAMBdlhCtbl4+ML0gViRwW7uII1mcKsiAUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Grade = _t, Average = _t, Grade1 = _t, Average1 = _t, Grade2 = _t, Average2 = _t, Grade3 = _t, Average3 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Grade", type text}, {"Average", Int64.Type}, {"Grade1", type text}, {"Average1", Int64.Type}, {"Grade2", type text}, {"Average2", Int64.Type}, {"Grade3", type text}, {"Average3", Int64.Type}}),
    Custom1 = List.Combine(Table.ToRows(#"Changed Type")),
    Custom2 = List.Zip({List.Alternate(Custom1, 1, 1, 1), List.Alternate(Custom1, 1, 1, 0)}),
    Custom3 = Table.FromRows(Custom2, type table [Grade = text, Average = number]),
    #"Filtered Rows" = Table.SelectRows(Custom3, each ([Grade] <> ""))
in
    #"Filtered Rows"

Basically you are creating a combining the lists of rows into a single list and then combining every other row together in a new list and then turning that list back into a table.

I start with...

and end up with...

hello, HerbertC 

    to_list = Table.ToList(
        your_table,
        (x) => List.Split(x, 2) 
    ),
    to_table = Table.FromList(
        List.Combine(to_list), 
        (x) => x, 
        {"Grade", "Average Price"}
    )
    // then filter out rows with nulls or blanks 

Thanks jgeddes 

The solution is not very far off. How can I attach an excel or CSV file so that you have the actuall data?

Regards

Herbert

HerbertC  

1. Import the Data: Open Excel and import the data from your email attachment or copy and paste it into a new worksheet.

2. Select the Data: Highlight the range of cells containing your data.

3. Open Power Query: Go to the "Data" tab in Excel and click on "From Table/Range" in the "Get & Transform Data" group. This will open the Power Query Editor.

4. Transform the Data:

   • In the Power Query Editor, you'll see your data displayed in a table.
   • Select the columns that contain the grades and prices.
   • Go to the "Transform" tab and click on "Unpivot Columns" in the "Any Column" group. This will unpivot your data and stack the grade and price columns into a single column called "Attribute" and another column called "Value".
   • Rename the "Attribute" column to "Grade" and the "Value" column to "Price".
   • You may need to do additional cleaning or formatting depending on your data.

5. Load the Transformed Data: Once you're satisfied with the transformation, click on "Close & Load" in the Home tab to load the data back into Excel.

6. Review the Data: After loading the transformed data back into Excel, you should see it in a format where you have two columns: one for the grade and one for the average price.
   
   Did I answer your question? Mark my post as a solution! Appreciate your Kudos !!

Hi HerbertC, different approach:

Before

After

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcjTy8VbSUTLSMwKSAcb+bo5A2sQUSEQY+7gDKVNjkAwIxepEK/kY+YDEzEzAqg3CgJQZVDFEtQlIdYSpQSSQMrcwAWvyNbAMBdlhCtbl4+ML0gViRwW7uII1mcKsiAUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Grade = _t, Average = _t, Grade1 = _t, Average1 = _t, Grade2 = _t, Average2 = _t, Grade3 = _t, Average3 = _t]),
    Transformed = Table.Combine(List.Transform(List.Split(Table.ToColumns(Source), 2), each Table.FromColumns(_, Value.Type(Table.SelectColumns(Source, List.FirstN(Table.ColumnNames(Source),2))))))
in
    Transformed

HerbertC Pls provide public access.