Import this xml into PBI Desktop:
<?xml version="1.0" encoding="utf-8"?> <table> <row> <AllValues>111</AllValues> <AllEmpty></AllEmpty> <Mixed1>abcd</Mixed1> <Mixed2>abcd</Mixed2> <Mixed3></Mixed3> </row> <row> <AllValues>222</AllValues> <AllEmpty></AllEmpty> <Mixed1></Mixed1> </row> <row> <AllValues>333</AllValues> <AllEmpty></AllEmpty> <Mixed1></Mixed1> </row> </table>
In Query editor the result is:
In the UI you cannot expand the columns AllEmpty and Mixed3, since PQ cannot find any columns in the empty tables.
Note the difference between Mixed1 and Mixed2, in row 1 they both have the same value 'abcd'. However, in Mixed1 this value is wrapped in a table.
Solution: Power Query should never wrap simple xml-elements in a table. An empty xml-element should always transform to a null, and so should a missing element.
Workaround: use a function like Chris Webb describes here (which fails for AllEmpty above, btw).
Solving this issue would make processing xml files in PBI and Excel a lot easier!!
Jan Update and Build 15002:DataFormat.Error: The input couldn't be recognized as a valid Excel documStatus: Accepted
I am building a couple of dashboards in desktop, done 60Hrs this week alone.
Working fine yestereday- all use excel files and tables as data sources
Today- Update inside windows 10 insiderPro build 15002 this morning
Update to Jan 2017 PowerBI
Now every dash won't update with the message
DataFormat.Error: The input couldn't be recognized as a valid Excel document.
Every file opens fine in excel not corrupted, even tried with new data, same message
I've rolled back to an earlier Power BI but get the same error, any other ideas welcome.
Is there a setting in the build 15002 that doesn't recognise excel files as excel files???
We are trying to use Power BI to pull data from SAP BW leveraging both BEx queries and cube connections. With each scenario we are running into different issues and were trying to figure out if this is a Power BI Limitation/Issue with 1. Connecting to a Warehouse 2. They way that Power BI is communicating with SAP BW to pull its data or is this a SAP BW Limitation/Issue based on how SAP BW servers up data.
Tested With Cube:
0FIGL_C10 -General Ledger (New) Transaction Figures
Data Load is very slow
Adding new columns and refreshing data seems to take a very long time. I don't know if this is a sap warehouse issue, an OLAP issue, or a power bi Issue. I had our basis team monitor our BW environment when connecting to a cube where I selected all the columns. The cube contains 349,628 records which is based on generating the SQL Statement from the cube (In SAP BW RSA1) and executing that in SQL Server Management Studio. This call eventually failed with the following ‘RFC_ABAP_RUNTIME_FAILURE: TSV_TNEW_PAGE_ALLOC_FAILED which I was told was due to running out of memory. Prior to testing this I had Basis increase our memory footprint on the server in an attempt to rule out memory issues.
Column limitation (selecting to many columns) or possibly and combination of Columns + Rows
When I connect to a cube and select 1-4 fields everything is pretty responsive. But as you start adding more and more fields the performance starts to suffer. In testing it looks like we can add 12 fields without any issue (Super Slow Refresh Times) but once we hit the 13th field we get an RFC_ABAP_RUNTIME_FAILURE – TIME_OUT.
I don’t write a lot of MDX statements so my statement could be the cause of the issue but I run into the same issues remain. Which makes sense because I believe Power BI is connecting to SAP BW through the BW Open Analysis Interface OLBAP BAPIs regarless of if you going againt a Cube, BEx Query, or MDX.
Select NON EMPTY [Measures].MEMBERS ON COLUMNS, NON EMPTY [0FISCYEAR].Members * [0CO_AREA].Members * [0COMP_CODE].[LEVEL01].Members PROPERTIES [0COMP_CODE].[20COMP_CODE] on rows from [$0FIGL_C10] WHERE ([0COMP_CODE]., [0FISCYEAR].[K12016]
Select NON EMPTY [Measures].MEMBERS ON COLUMNS, NON EMPTY [0FISCYEAR].Members * [0CO_AREA].Members * [0COSTCENTER].Members * [0CHRT_ACCTS].Members * [0CO_AREA].Members * [0CURTYPE].Members * [0FUNC_AREA].Members * [0GL_ACCOUNT].Members * [0PROFIT_CTR].Members * [0SEGMENT].Members * [0VALUATION].Members * [0VERSION].Members * [0FISCPER].Members * [0FISCPER3].Members * [0FISCVARNT].Members * [0FISCYEAR].Members * [0COMP_CODE].[LEVEL01].Members PROPERTIES [0COMP_CODE].[20COMP_CODE] on rows from [$0FIGL_C10] WHERE ([0COMP_CODE]., [0FISCYEAR].[K12016]
Key and Text Values
The last think I want to touch on is the key values. Power BI does not appear to give you the ability to select or toggle between the text and key values. Though MDX I was able to include the properties section and include the key value (Which was nice) but this should be standard functionality.
I wanted to try and cover all my bases and rule out as much as I could to try and pin point the issue. As such I used Tableau to connect to the same structure as it’s my understanding that it also utilizing the BW Open Analysis Interface OLBAP BAPIs to query SAP BW. I’ll point out that I don’t really use Tableau but it was a tool that was available to use a test. Once again I had Basis monitor the SAP Environment to help track down any issues. Within seconds tableau had pulled in the Metadata and I was able to create a sheet. Tableau appears to provide a live query connection to the cube whereas Power BI appears to try and load the data first which may also be contributing to the performance issues. Again I just wanted to test with another tool to try and rule out the SAP environment.
With SAP being our ERP system we need the ability to effectively be able to query our data warehouse and provide the user community with self-service reporting/analytics. With the issues listed above its making this a very difficult process to move forward with. Any ideas or suggestions would be greatly appreciated. We love Power BI and the users who have seen our demos love what it can do and I feel like we are always installing this on someone’s machine.
I was very excited when wrap column headers was introduced in the January 17 update. Then very dismayed to find once published it was not supported.
Please tell me I am missing a trick, or alternatively when I can expect it to be supported?
When using dashboard tiles, one can set a custom link to have the tile navigate to any URL. Currently in the Service, if the Custom URL is set, and the "Open Custom Link in Same Tab" is checked to "Yes", then the following behavior is experianced.
Test case: Custom URL used to navigate to a different report page rather than the one the tile was pinned from. "Open Custom link In Same tab is checked to "Yes". The tile is clicked and the user does not go anywhere.
I tested further, and if I add a report URL from a different Power BI report, the behavior of the tile is such that on click, it takes me to the other report dashboard where visuals were pinned from the report.
It would appear, that the Custom URL link is not allowing me to access the report URL when I have set it to open in the same tab, but rather rolls me up to the Dashboard.
If the "Open Custom Link in Same Tab" is checked to "No" - the tile works as expected.
We don't receive mails from some of the "send mail" features of the Power BI cloud service.
We know that there is no problem on our side with receiving mails, because, in general, we DO get mails, e.g. when:
- being added to a group
- when a dashboard is shared with me
But we don't get mails at least on these occasions:
- data alerts
- report subscription
There are several posts on that issue and none of them has a found a solution so far:
I wonder if the problem only occurs if you are in an enterprise context and your UPN is not the same as your email address. In my case, if the system sends mails to my UPN, i will never receive them.
Here is some more of what I found out:
Using a "Alternate E-Mail-Address": In an enterprise context, I'm not able to set and use an alternate email-address as suggested here. That's because I'm not allowed to change my information settings due to organizational reasons.
But IF i could, there's another problem: According to our AD guys, the "Alternate email" information is stored in an AD-field named "othermails", which can only be set in the Admin-UI if the user got a special role assigned to. But that does not apply, if the user is a simple PBI-User (and no PBI-Admin for example). So putting email addresses into the "alternate email" will fix it only for special users.
Additionally, the "othermails" field doesn't seem to get synced from AD to the AAD. That means that putting email-adresses into that On-Prem-AD-Field wouldn't change a thing for the cloud service.
If the Power BI-function that sends alerting notification uses either the UPN or the "othermails" field, emails get lost. There's a AD field called "mail", which could be used, but perhaps it isn't, at least in some cases.
Sorry if some of the information above is perhaps specific to our case. (after all I'm a BI guy, no AD expert)
If I simply missed something - any help will be appreciated!
I have 1 workspace collection with 8 workspaces. In each workspaces, there is one datasets. However, when I update my new credentials of a datasets. The new credentials overwrite all other credential within the workspace collection.
It seems like all dataset are sharing a credentials in the workspace collection. For new imported dataset, It will automatically adopted pre-set credentials.
I am using Azure SQL + Row level security to limit the data access by different users. If only 1 credential is allowed in workspace collection, all users will be access Azure SQL database with same credential (This is not what I want).
In Dec 2016, I can set different credential for each dataset. So that, different user can access Azure SQL with different credential. I developed my web app on powerBI embeded for more than 6 months. It is not working now.
Please help to resolve this issues as soon as possible.
We use background images to improve the visual appearance of our reports. The images used to resize based on the size of the browser window. However, recently the images stopped resizing proportionally to the other visuals. This occurs in the service and mobile views. It occurs in reports and dashboards.
To illustrate this issue, place an image on a report spanning the entire report. Add some visuals on top of the image. Make your browser window smaller. The visuals get a little smaller but the image gets tiny and the visuals no longer "fit" within the image.
I'm working on a dashboard for my team on a Call Center, I'm looking to show their AHT against the goal in a KPI visual, However, it displays correctly in the Desktop app but when I publish it, it will show a legend (Blank) for both, the current and the goal values.
This also happens with string values. With any other data type it shows correctly.
My problem have occured some days ago. Some tiles from my dashboards have lost links to the report on click. If I click on the part of them it have link, some of them no - nothing happened on click. The problem repeats as on new dashboards as with old ones. I did nothing with them - no new tiles, no publishes or sharing. Need help. thanks
We are uploading .pbix files to OneDrive for Business and connecting to them through PowerBI web.
The .pbix files use custom-build OData-feeds (with HTTP Basic-Auth) as data-sources. We've been succesfully utilizing these OData-Feeds for about a year now.
However, since about 2 weeks ago, all scheduled refreshes started failing due to authentication errors. Here's an example:
Last refresh failed: Wed Dec 21 2016 16:23:53 GMT+0100 (W. Europe Standard Time)
There was an error when processing the data in the dataset.
|Data source type:|
|Processing error:||The credentials provided for the OData source are invalid. (Source at https://<<confidential>>)|
When looking at the server-logs, I can confirm that PowerBI sends the wrong credentials in the "Authorization" HTTP-header.
I'm unable to determine the exact nature of the authentication payload, but it looks like a OneDrive authentication-flow.
Furthermore, PowerBI consistantly sends invalid credentials for a certain type of feed and always on the request after the pagination of the feed has been exhausted, therefore making it quite clear that this is a bug.
If the support for PowerBI Web gets in touch, I can provide exact steps to reproduce the Issue, including the offending sources, credentials and server logs.
Exporting the data from a table/matrix to CSV has the proper column order that matches the table.
However, in the service, export to XLSX seems to randomly re-order the columns.
Shown is an image with all 3 column headers. The service on top, CSV (with matching header order) in the middle, XLSX on the bottom with random order headers.
This is coming from Project Online, however I am able to replicate using multiple data sources including SQL Server.
I use the custom visual "Bullet Chart" by Microsoft: If one switches on the "drill mode" of the visual (the arrow on the top right is black), one should be able to drill down by clicking on any data point, if there is a hierarchy in the "categories" field.
But it dowsn't. The visual shows the same as before. The names of the hierarchy levels is added to the title each time i click on an data point, though. The button "go to the next level of the hierarchy" works correctly, too.
Before clicking on the bar "2016":
After clicking on the 2016 bar, it's selected and the hierarchy level name "Month" is added to the title. No drill down occured:
What do I do wrong? In case it's a bug - please fix it! :-)
Power BI Team,
We have been unable to publish PBIX files via Power BI Desktop to Workspaces apart from 'my workspace'.
Prior to 1pm AEST, it was working fine. However, I am getting a number of calls from staff with the same problem:
Publishing to Power BI: An error occurred while attempting to publish 'Floor Plan.pbix': The remote server returned an error: (400) Bas Request.
We are using the Sydney Data Centre.
We are experiencing an issue on the dashboard with tiles showing "The data shown in this tile is no longer available. Please contact the report owner for details" despite the dashboard being still available. Once the user refreshes the page, the tile loads up fine. The user is consistently getting this error.
While using Q&A against an OLAP cube, as soon as I type "by" something it creates a random filter against the visual causing it to fail.
As soon as I delete the filter it works as it should. This happens with all measures & dimensions and I have tried 3 seperate cubes all with the same result. Is this a known bug or just a result of "Some functionality is available for multi-dimensional data sources, but the full Q&A experience is not yet supported for this dataset type."?