Fixed Width File not Loading all rows

[stretcharm]

Hi,

Has anyone seen this issue before.

I'm trying to load a large 550Mb fixed width file with M into PBI Desktop and it only loads 19,645 out of 226,559 rows

I tried a different file and got the exact same number of rows.

It's got lots of columns but it doesnt seem change if only bring in the first few.

let
    Source = Csv.Document(File.Contents("MyFile.txt"),7,{0, 3, 22, 41, 43, 44, 45},null,1252),
    #"Removed Columns" = Table.RemoveColumns(Source,{"Column7"}),
    #"Grouped Rows" = Table.Group(#"Removed Columns", {}, {{"Count", each Table.RowCount(_), type number}, {"lastCust", each List.Max([Column2]), type text}})
in
    #"Grouped Rows"

If I load without spliting it loads all 226K rows

let
    Source = Table.FromColumns({Lines.FromBinary(File.Contents("MyFile.txt"), null, null, 1252)}),
    #"Grouped Rows" = Table.Group(Source, {}, {{"Count", each Table.RowCount(_), type number}})
in
    #"Grouped Rows"

I'm only using 30% of my Ram.

Anyone got any thoughts on why it might be limiting the rows?

I suspect a bad character, but cannot see anything with notepad++ around that row.

 The Guys at powerpivotpro seem have a workaround that could help, but still curious what is stopping the fixed width load.

https://powerpivotpro.com/2016/03/power-query-fixed-width-magic/

let
    Source = Table.FromColumns({Lines.FromBinary(File.Contents("MyFile.txt"), null, null, 1252)}),
    #"Split Column by Position" = Table.SplitColumn(Source,"Column1",Splitter.SplitTextByPositions({0, 3, 22, 41, 43, 44, 45}, false),{"Column1", "Column2", "Column3", "Column4", "Column5"}),
    #"Grouped Rows" = Table.Group(#"Split Column by Position", {}, {{"Count", each Table.RowCount(_), type number}, {"MaxCust", each List.Max([Column2]), type text}})
in
    #"Grouped Rows"

Phil

[v-yulgu-msft]

Hi @stretcharm,

Based on my research, to load data from a fixed width file, we have to split a single column into multiple columns, as mentioned in above link. For how the Power Query load data and why it doesn't load all records without spliting column, I would suggest you post question on Power Query forum for professional advice.

Regards,
Yuliana Gu 

[stretcharm · Accepted solution]

Thanks.

I rewrote it by loading then splitting and I got all 226K rows.