Forum Discussion

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

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

HerbertC

Regular Visitor

2 years ago

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

Super User

2 years ago

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

Regular Visitor

2 years ago

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=102696255102699655852&rtpof=true&sd=true

Thank you so much

Regards

Herbert

dufoq3

Community Champion

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

HerbertC
Regular Visitor
2 years ago
@jgeddes @dufoq3 @johnbasha33 @AlienSx

Pse try again
- dufoq3
  Community Champion
  2 years ago
  Make the link public please.

Forum Discussion

Split Data In Multiple Columns

Related content

Splitting Column values

Splitting columns into multiple rows

Split a column into two separate columns

Splitting multiple columns using delimiter

Split multiple columns into one row