Power Query Faulting

[Luciano360]

Hello.

I was a fan of Microsoft Access until I discovered Power Query.

Despite my enthusiasm for PQ, I am find some issues that I cant seem to figure out.

1.  A switchboard I am trying to implement does not seem to be updating quickly enough and potentially not at all.  All data is saved locally on my hard drive so it is not a connectivity issue.

2.  I discovered with a data set that I am pulling down from an web based reporting tool, that any negative dollar values are excluded and removed before any manipulation of data.  This is causing inaccurate aggregation of $ and misrepresenting what I am trying to present.

Any help and direction would be greatly appreciated.

[#2]

@Luciano360 

1) Power Query performance depends on many things, that's not only which data source you use. As an example you may check Chris Webb's BI Blog: Optimising The Performance Of Power Query Merges In Power BI, Part 1: Removing..., here are a lot more related posts.

2) Could you provide some sample? How exactly do you aggregate, field types, etc.

[#3]

@Sergei Baklan 

Thank you for your prompt attention... 

Concerning the issue of removing negative amounts I I would like to share the work, but I am not sure how to do so on this forum.

While "playing" with this data, I noticed that when the data is reading to an outside source the problem arises, however, if the raw data is resident in the same file, the queries run as they should.

The preferred method for managing the data would be the raw data as a seterate file since it would get updated routinely and I would like to keep all the query data in tact

[#4]

@Luciano360 

If you may share the file that will be great. It could be an option to attach the file to the post

If it is not available for please send me with Private Message here, I will re-share.

In any case please remove any sensitive information if exists.

[#5 · Accepted solution]

@Luciano360 

Here is the summary for other people regarding negative numbers.

- we query external binary file (.xlxb) and Power Query ignores minus for negative numbers

- actually, even if we use the same From File menu, behind for binary file Power Query use another connector. Same as for Access Database (you may try From Access Database and navigate on Excel binary file, result will be the same as for From File).

- this connector returns masked by applied format numbers, not actual ones. Other words close to what in Excel is returned by =TEXT( number, format string ), but not exactly the same

- In particular file to numbers was applied custom number format [=0]#,##0;#,##0

- it looks like Power Query doesn't recognize correctly leading [=0] and ignores minus returning the number

- if apply simple #,##0 format or like, Power Query works correctly with negative numbers

In general where possible better to work with .xlsx files. From performance point of view even better with .csv

[#6]

Thanks You!

I changed the source file to .xlsx and it seems to have resolved the issue!

Many thanks!