cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
stretcharm Senior Member
Senior Member

Fixed Width File not Loading all rows

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

1 ACCEPTED SOLUTION

Accepted Solutions
stretcharm Senior Member
Senior Member

Re: Fixed Width File not Loading all rows

Thanks.

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

 

2 REPLIES 2
v-yulgu-msft Super Contributor
Super Contributor

Re: Fixed Width File not Loading all rows

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 

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
stretcharm Senior Member
Senior Member

Re: Fixed Width File not Loading all rows

Thanks.

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

 

Helpful resources

Announcements
Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Power BI Helps Homeless and Trouble Youth

Power BI Helps Homeless and Trouble Youth

We spoke with Power BI Super User, Greg Deckler, about his charity work

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 119 members 1,585 guests
Please welcome our newest community members: