Forum Discussion

TMyers790's avatar
TMyers790
Copper Contributor
Aug 18, 2022
Solved

All data for 2 columns contained in one cell in each column

I am trying to bring in data from a PDF file (it is a converted excel file so it plays nice for the most part) but one of the tables I am trying to import isn't reading right and all the data is cram...
Excel
  • Lorenzo's avatar
    Lorenzo
    Aug 19, 2022

    TMyers790 

    Thanks Riny_van_Eekelen for bringing the fact that I abused of List.Accumulate + there was an easier solution. A variant of Riny_van_Eekelen's approach:

    let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    SplitEachColumns = List.Transform(
        List.Combine(
            Table.ToColumns(Source)
        ), each Text.Split(_, "#(lf)")
    ),
    TableFromColumns = Table.FromColumns(SplitEachColumns, Table.ColumnNames(Source))
in
    TableFromColumns

     

     

Riny_van_Eekelen's avatar
Riny_van_Eekelen
Platinum Contributor
Aug 19, 2022

TMyers790 As a variant, please see the attached file, containing a small script that works for any size table.

 

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    TblToCol = Table.ToColumns (Source),
    Convert = Table.FromList(TblToCol, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    Expand = Table.ExpandListColumn(Convert, "Column1"),
    Split = Table.AddColumn(Expand, "Custom", each Text.Split([Column1], "#(lf)")),
    TblFromCol = Table.FromColumns (Split [Custom])
in
    TblFromCol

 

 

Resources