PQ to extract only dates or numbers per row

[brawnystaff]

I OCR'ed a PDF containing a printout from ledger data and put it into an Excel worksheet. However, the OCR process puts the data in differnet columns depending on the page, as there was alot of text in it that I do not need.

The PDF ledger contained about 10 columns, and the data I want is either a date or number (sometimes with a decimal, sometimes not) in their respective cell. Other cells that contain either words or words with letters I do not need.

Is there a way in Power Query to extract out only cells with dates or numbers for each row? The PQ editor is showing 18 columns, as the "junk text" is inner-mixed in each row depending on the page. Looking to only extract cells with dates or numbers to only get the 10 columns. Any ideas? Thanks.

 

[smozgur]

I am not sure what are the other options to do this, but this is my attempt:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    // Get the column names as a list
    Fields = Table.ColumnNames(Source),
    // Transform operation for the column transformation
    // Basically checking each field against its value type 
    Transformer = List.Transform(Fields, each {_, each if Value.Is(_, type date) or Value.Is(_, type datetime) or Value.Is(_, type number) then _ else null }),
    // Apply the transformation to mark invalid values as null 
    MarkValids = Table.TransformColumns(Source, Transformer),
    // Filter the columns to pick values other than null
    // Assumption is each row has the same number of columns with valid values 
    Filter = Table.TransformRows(MarkValids, each 
        List.Accumulate({0..Record.FieldCount(_) - 1},
                {},
                (state, current) => let 
                                        Field = Record.FieldNames(_){current},
                                        This = Record.SelectFields(_, Field),
                                        Result = if Record.Field(_, Field) is null then state else state & Record.ToList(This) 
                                    in
                                        Result
        )
    ),
    // Create a list of filtered records
    // Generating dynamic field/column names 
    List = List.Transform(Filter, each Record.FromList(_, List.Transform({1..List.Count(_)}, each "field" & Number.ToText(_)))),
    // Create a table contains the filtered records
    CreateTable = Table.FromList(List, Splitter.SplitByNothing(), {"Column"}, null, ExtraValues.Error),
    // Expand the records in table rows
    // Again, using the dynamically created field names
    Result = Table.ExpandRecordColumn(CreateTable, "Column", List.Transform({1..Record.FieldCount(CreateTable{0}[Column])}, each "field" & Number.ToText(_)))
in
    Result

I have this sample table:

field1	field2	field3	field4	field5	field6	field7
1/1/2012	c	3	asd	asd	3	asdasd
d	1/2/2012	5	asd	fsd	ewr	2
1/3/2012	sff	6	1	3	wer	asdasd
g	sdasd	1/4/2012	sdd	7	cxcz	55
1/5/2012	ert	sdf	zxc	5	4	ads
wre	1/6/2012	dfg	sdf	dfg	7	99
1/7/2012	fggf	dfg	ada	8	8	asdasd
rt	dfg	1/8/2012	sdf	sfd	2	2

And the code generates the following output.