Power Query Import

%3CLINGO-SUB%20id%3D%22lingo-sub-2186941%22%20slang%3D%22en-US%22%3EPower%20Query%20Import%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2186941%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20a%20data%20table%20with%2025%2C000%20rows.%20Power%20Query%20reads%20the%20table%20and%20all%20the%20rows%20but%20since%20only%201%2C211%20of%20the%20data%20points%20in%20those%20rows%20are%20decimal%2C%20Power%20Query%20treats%20the%20values%20as%20whole%20numbers%2C%20even%20after%20transforming%20the%20column%20(Change%20Type).%20Then%20if%20I%20know%20that%20I%20need%20to%20%22Load%20More%20Data%22%2C%20it%20will%20sometime%20work.%20I%20believe%20it%20depends%20on%20if%20the%20Change%20Type%20was%20accepted%20as%20a%20replace%20current%20or%20add%20new%20step.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2186941%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2187185%22%20slang%3D%22en-US%22%3ERe%3A%20Power%20Query%20Import%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2187185%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F704123%22%20target%3D%22_blank%22%3E%40Matt_Ferguson%3C%2FA%3E%26nbsp%3BIt%20is%20my%20understanding%20that%2C%20by%20default%2C%20PQ%20detects%20column%20data%20types%20the%20first%20time%20after%20it%20connects%20to%20a%20data%20source.%20That's%20demonstrated%20by%20the%20fact%20that%20it%20automatically%20adds%20the%20%22Changed%20Type%22%20step%20in%20the%20list%20of%20applied%20steps.%20But%20PQ%20isn't%20always%20correct%2C%20as%20it%20only%20looks%20at%20the%20first%201000%20rows%20of%20data.%20So%2C%20always%20assure%20yourself%20that%20the%20data%20types%20are%20set%20correctly.%20It%20doesn't%20matter%20if%20you%20do%20it%20by%20replacing%20the%20current%20setting%20or%20adding%20a%20new%20step.%20I%20always%20replace%20the%20current%20as%20I%20don't%20like%20to%20add%20extra%20steps%20when%20not%20needed.%20Anyhow%2C%20whatever%20data%20type%20was%20set%20last%2C%20that's%20what%20PQ%20will%20work%20with.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20you%20find%20that%20you%20frequently%20need%20to%20correct%20PQ's%20automatic%20data%20typing%2C%20you%20can%20switch%20it%20off%20under%20Query%20Options%2C%20Global%2C%20Data%20Load%20and%20select%20%22Never%20detect%20column%20types.................%22%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Visitor

I have a data table with 25,000 rows. Power Query reads the table and all the rows but since only 1,211 of the data points in those rows are decimal, Power Query treats the values as whole numbers, even after transforming the column (Change Type). Then if I know that I need to "Load More Data", it will sometime work. I believe it depends on if the Change Type was accepted as a replace current or add new step.

1 Reply

@Matt_Ferguson It is my understanding that, by default, PQ detects column data types the first time after it connects to a data source. That's demonstrated by the fact that it automatically adds the "Changed Type" step in the list of applied steps. But PQ isn't always correct, as it only looks at the first 1000 rows of data. So, always assure yourself that the data types are set correctly. It doesn't matter if you do it by replacing the current setting or adding a new step. I always replace the current as I don't like to add extra steps when not needed. Anyhow, whatever data type was set last, that's what PQ will work with.

 

If you find that you frequently need to correct PQ's automatic data typing, you can switch it off under Query Options, Global, Data Load and select "Never detect column types................."