Split Data In Multiple Columns

[HerbertC]

Good evening folks,

I have data that is emailed to me daily with a grade and the price of the grade. My challenge is that the data is arrange in double columns that go across the page. How can I convert this data into just 2 columns, ie grade and average price? Please see example below, I wasn't sure how to attach a pdf or excel file. Any assistance greatly appreciated.

I would like to convert this into 2 columns iei grade and price

Regards 

Herbert

[mahenkj2]

@HerbertC Pls provide public access.

[dufoq3]

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

[johnbasha33]

@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 !!

[HerbertC]

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

[jgeddes]

The easiest way to share a file is to post a link to a file saved in cloud storage such as OneDrive. Make sure the link allows access to the file. 
Another method for sample data is that you can copy a section of data frpm your file and pasting it into the table feature of the reply headers of these messages.

[AlienSx]

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 

[jgeddes]

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...

[HerbertC]

Thank you very much @jgeddes this is certainly getting me warmer.

I am a bit of a newbie, would you mind assisting to add your query to the one below, which I have extracted. The query below brings me to the starting point in power query which is equivalent to the pic that I originally posted:

let
    Source = Folder.Files("C:\Users\XXXXX\Documents\XXX\Reporting & Analysis\XXXXXX Matrix\Data"),
    #"Filtered Hidden Files1" = Table.SelectRows(Source, each [Attributes]?[Hidden]? <> true),
    #"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File", each #"Transform File"([Content])),
    #"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),
    #"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File"}),
    #"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File", Table.ColumnNames(#"Transform File"(#"Sample File"))),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded Table Column1",{{"Source.Name", type text}, {"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}, {"Column8", type text}, {"Column9", type text}, {"Column10", type text}, {"Column11", type text}, {"Column12", type text}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Column2] <> null)),
    #"Promoted Headers" = Table.PromoteHeaders(#"Filtered Rows", [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"02-APR-2024.pdf", type text}, {"Grade", type text}, {"Average Price", type text}, {"Grade_1", type text}, {"Average Price_2", type text}, {"Grade_3", type text}, {"Average Price_4", type text}, {"Grade_5", type text}, {"Average Price_6", type text}, {"Grade_7", type text}, {"Average Price_8", type text}, {"Grade_9", type text}, {"Average Price_10", type text}}),
    #"Filtered Rows1" = Table.SelectRows(#"Changed Type1", each ([Grade] <> "Grade")),
    #"Renamed Columns" = Table.RenameColumns(#"Filtered Rows1",{{"02-APR-2024.pdf", "Date"}})
in
    #"Renamed Columns"

Thank you

[jgeddes]

This should do it...

let
    Source = Folder.Files("C:\Users\XXXXX\Documents\XXX\Reporting & Analysis\XXXXXX Matrix\Data"),
    #"Filtered Hidden Files1" = Table.SelectRows(Source, each [Attributes]?[Hidden]? <> true),
    #"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File", each #"Transform File"([Content])),
    #"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),
    #"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File"}),
    #"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File", Table.ColumnNames(#"Transform File"(#"Sample File"))),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded Table Column1",{{"Source.Name", type text}, {"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}, {"Column8", type text}, {"Column9", type text}, {"Column10", type text}, {"Column11", type text}, {"Column12", type text}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Column2] <> null)),
    #"Promoted Headers" = Table.PromoteHeaders(#"Filtered Rows", [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"02-APR-2024.pdf", type text}, {"Grade", type text}, {"Average Price", type text}, {"Grade_1", type text}, {"Average Price_2", type text}, {"Grade_3", type text}, {"Average Price_4", type text}, {"Grade_5", type text}, {"Average Price_6", type text}, {"Grade_7", type text}, {"Average Price_8", type text}, {"Grade_9", type text}, {"Average Price_10", type text}}),
    #"Filtered Rows1" = Table.SelectRows(#"Changed Type1", each ([Grade] <> "Grade")),
    #"Renamed Columns" = Table.RenameColumns(#"Filtered Rows1",{{"02-APR-2024.pdf", "Date"}}),
    Custom1 = List.Combine(Table.ToRows(#"Renamed Columns")),
    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 Rows2" = Table.SelectRows(Custom3, each ([Grade] <> ""))
in
    #"Filtered Rows2"

[HerbertC]

Good day @jgeddes @dufoq3 @johnbasha33 @AlienSx 

Thank you all for your contributions so far, almost there but not quite.

I am sharing a link with my actual excel spreadsheet once I have done all the transformations that I can.

May you please assist with how to shift from here to end up with just 3 columns, i.e. 1. Source (Date), 2. Grade & 3. Price.

https://docs.google.com/spreadsheets/d/16en6fo7n-5VR-virg1DoezUzsRmHmYfi/edit?usp=drive_link&ouid=10... 

Thank you so much

Regards

Herbert

[dufoq3 · Accepted solution]

Hi, if you don't need PDF name use this code:

let
    // Google Drive Link
    Source = Web.Contents("https://drive.google.com/uc?export=download&id=16en6fo7n-5VR-virg1DoezUzsRmHmYfi"),
    ExcelWorkbook = Excel.Workbook(Source),
    MatrixData_Table = ExcelWorkbook{[Item="MatrixData",Kind="Table"]}[Data],
    Transformed = Table.Combine(List.Transform(List.Split(List.Skip(Table.ToColumns(MatrixData_Table)), 2), each Table.FromColumns(_, Value.Type(Table.SelectColumns(MatrixData_Table, List.FirstN(List.Skip(Table.ColumnNames(MatrixData_Table)),2))))))
in
    Transformed

If you want to preserve PDF names, use this code:

let
    // Google Drive Link
    Source = Web.Contents("https://drive.google.com/uc?export=download&id=16en6fo7n-5VR-virg1DoezUzsRmHmYfi"),
    ExcelWorkbook = Excel.Workbook(Source),
    MatrixData_Table = ExcelWorkbook{[Item="MatrixData",Kind="Table"]}[Data],
    GroupedRows = Table.Group(MatrixData_Table, {"Date"}, {{"All", each Table.Combine(List.Transform(List.Split(List.Skip(Table.ToColumns(_)), 2), (x)=> Table.FromColumns(x, Value.Type(Table.SelectColumns(_, List.FirstN(List.Skip(Table.ColumnNames(_)),2)))))), type table}}),
    ExpandedAll = Table.ExpandTableColumn(GroupedRows, "All", {"Grade", "Average Price"}, {"Grade", "Average Price"}),
    FilteredRows = Table.SelectRows(ExpandedAll, each ([Grade] <> null and [Grade] <> "")),
    ChangedType = Table.TransformColumnTypes(FilteredRows,{{"Grade", type text}, {"Average Price", Currency.Type}}, "en-US")
in
    ChangedType

[HerbertC]

@jgeddes @dufoq3 @johnbasha33 @AlienSx 

Pse try again

[dufoq3]

Make the link public please.

[HerbertC]

@dufoq3 @mahenkj2 @jgeddes @johnbasha33 @AlienSx 

My apologies, I have made the link public.

https://drive.google.com/drive/folders/1yLfb9-q0VF77zJDcVMyIp4VHgI0wHSIK?usp=sharing 

Thank you

[dufoq3]

I've already solved your task 30 minutes ago... See it here if you don't want to scrolll