PQ data type change when pulling from xls file in sharepoint folder

[didijaba]

HI,
thanks for reading my post, hope you'll have advice how to solve this. I have xls files in sharepoint folder. All files have same structure.
Data in sheets is not in formated tables, so there are text and number cells (general type). Numbers are decimal (example 2.33 shown, in real number is 2.326546678).
When I pull data using Power Query (Excel 2016 64x) numbers become 2.33, and all other is lost?
Any idea how to get full numbers.
Thanks in advance.

let
    Source = SharePoint.Files(MAIN_SOURCE, [ApiVersion = 15]),
    #"Filtered Rows" = Table.SelectRows(Source, each Text.Contains([Folder Path], "TEST_PQ")),
    #"Filtered Rows1" = Table.SelectRows(#"Filtered Rows", each Text.Contains([Name], "_Oriel")),
    #"Invoke Custom Function1" = Table.AddColumn(#"Filtered Rows1", "Transform Binary from Query1", each #"Transform Binary from Query1"([Content])),
    #"Removed Other Columns1" = Table.SelectColumns(#"Invoke Custom Function1",{"Name", "Transform Binary from Query1"}),
    #"Expanded Transform Binary from Query1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform Binary from Query1", {"Name", "Data"}, {"Transform Binary from Query1.Name", "Transform Binary from Query1.Data"}),
    #"Expanded Transform Binary from Query1.Data" = Table.ExpandTableColumn(#"Expanded Transform Binary from Query1", "Transform Binary from Query1.Data", {"Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9", "Column10", "Column11", "Column12", "Column13", "Column14", "Column15"}, {"Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9", "Column10", "Column11", "Column12", "Column13", "Column14", "Column15"})

in
    #"Expanded Transform Binary from Query1.Data"

 

[Matt Allington]

When I pull data using Power Query (Excel 2016 64x) numbers become 2.33, and all other is lost?

what does "..all other is lost?" mean?

 

[didijaba]

There is no numbers behind second decimal place, for example, at source number looks like 2.3456789, and after pulling it in using Power Query it looks like 2.350000000000. My guess is that it pulls it in as text, but I do not know how to bypass this.

 

[Matt Allington]

MS recently enhanced the expand binaries feature to improve the breadth of capability. Have you written a custom function? T seems there is one, so if not you, then possibly it was created by MS. Also MS is quick to define data types early in the process, and this can cause the problem you describe. So my suggestion is to look at the custom function, find the step (presumably) that sets the data type and change it there.

 

[didijaba]

I have read Power Query Archives - The Ken Puls (Excelguru) BlogThe Ken Puls (Excelguru) Blog and there is interesting thing about data sampling and data type choices. Thing is Excel is behind PBD, so I'll try and see what comes out of it. Thanks for quick replay