Aug 18 2022 05:16 AM - edited Aug 18 2022 05:29 AM
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/0 | Call |
2/0-2/0-2/0 | $10,030.72 |
3/0-3/0-3/0 | $12,698.63 |
4/0-4/0-4/0 | Call |
This is separated into nice neat rows.
Aug 18 2022 10:04 PM
Logic in attached file: Take each column individually, split it, add an index column, merge the 2 tables on [Index]
Aug 19 2022 12:29 AM
Aug 19 2022 01:06 AM - edited Aug 19 2022 01:06 AM
@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
Aug 19 2022 02:42 AM - edited Aug 19 2022 08:51 AM
SolutionThanks @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
Aug 19 2022 10:08 AM
Aug 19 2022 10:19 AM
Aug 19 2022 02:42 AM - edited Aug 19 2022 08:51 AM
SolutionThanks @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