Forum Discussion
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 ...
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 TransformedIf 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
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.
Thank you so much
Regards
Herbert
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