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

     

jgeddes's avatar
jgeddes
Icon for Super User rankSuper User
2 years ago

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's avatar
    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's avatar
      jgeddes
      Icon for Super User rankSuper 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"