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 crammed into a single cell in each column (shown below)

If you were to expand the two cells, the data actually lines up with their given value (10-10-10 is $1,128.29 etc).The problem I have is when I try to split this into rows, I can do one column but once I try the second I get a cartesian product which isn't helpful either.

 

So how can I break this mess into two columns that is not a cartesian product?

Edit:

To help better illustrate the problem, here is what it looks like what nothing is edited (all crammed into 2 cells):

SEU(Do not cut)

10-10-10
8-8-8
6-6-8
6-6-6
4-4-6
4-4-4
3-3-5
3-3-3
2-2-4
2-2-2
1-1-1
1/0-1/0-1/0
2/0-2/0-2/0
3/0-3/0-3/0
4/0-4/0-4/0		$1,128.29
$1,327.31
$1,765.84
$2,007.43
$2,791.64
$3,148.51
$3,672.23
$3,929.33
$4,255.35
$4,901.11
$6,471.35
Call
$10,030.72
$12,698.63
Call

 

Here is what I am trying to change it to:

SEU(Do not cut)

10-10-10$1,128.29
8-8-8$1,327.31
6-6-8$1,765.84
6-6-6$2,007.43
4-4-6$2,791.64
4-4-4$3,148.51
3-3-5$3,672.23
3-3-3$3,929.33
2-2-4$4,255.35
2-2-2$4,901.11
1-1-1$6,471.35
1/0-1/0-1/0Call
2/0-2/0-2/0$10,030.72
3/0-3/0-3/0$12,698.63
4/0-4/0-4/0Call

This is separated into nice neat rows.

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

     

     

  • Lorenzo's avatar
    Lorenzo
    Silver Contributor
    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

     

     

      • Lorenzo's avatar
        Lorenzo
        Silver Contributor
        Aug 19, 2022
        Glad this works as expected and we could help + Thanks for providing feedback
  • 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