Forum Discussion

HerbertC's avatar
HerbertC
Regular Visitor
2 years ago
Solved

Split Data In Multiple Columns

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 ...
Need Help
  • dufoq3's avatar
    dufoq3
    2 years ago

    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

     

johnbasha33's avatar
johnbasha33
Super User
2 years ago

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