It seems as if only Text is available to select. We require the Key to be available to build relationships and to view data
42 Comments
- This feature was released in the Power BI Desktop October update. Please take a look at the official release announcement for more details: https://powerbi.microsoft.com/en-us/blog/power-bi-desktop-october-2017-feature-summary/#sapBW
- Yes. We need to select key or/and text for characteristics based on what BW BEx query has. Currently no matter how I choose Key or/and Text, the SAP BW Connector always returns TEXT for the characteristics and KEY value is missing. Please help. Thanks.
- Agreed, the SAP connector is not much of use without this possibility. Might work for simple top level reports but for building relationships or analyzing deeper level data we really need this! This is basically the only point making us hold back on Power BI and considering other alternatives instead.
- I might have two workarounds. They are both cumbersome but they do work. 1) Use APD to create a CSV version of your query. This solution is also recommended by others regarding this same issue. 2) Create an additional DSO where you replace important fields by tekst-type fields (ie 0TXTSH). Next, place a query on top of this DSO (with OLEDB flag set). This will give you the key-values instead of the text-value. Like I said, its cumbersome but it works.
- This would be a much needed functionality
- Great new that you will support this. I'm very curious about how you will implement it. One example is the SKU's where the key is commonly the article number and the text is the name of the item. Both these fields are very import to be usable in reports, for example for filtering.
- Note that there is a workaround involving just the Query Editor: MDX member IDs can be retrieved via an M function and a reference to the column that has the caption. Need to type Cube.AttributeMemberId([YOUR_COLUMN]) in the “Add Column -> Custom Column” dialog in the Query Editor. This will return a set of values, which are not exactly the same as the Member Key, but it should be enough to disambiguate members as the values are unique (as opposed to the Captions). This should be applied as a last step as further operations on the result set (filters, etc.) will not be pushed to the underlying BW server. Hope this helps - We're actively working on the "first class" support for selecting Key in the Navigator dialog, but hopefully this will unblock you in the interim. Thanks, M.
- Any news?
- 'Just a heads up, I just tried the work around posted by Miguel below and it works but does require you to strip some values from the field. I used Text.Replace to strip the unwanted fields in order to get the actual key value. Example of adding Material Key Power BI: Query Editor -> Add Column -> Custom Column Option 1 =Cube.AttributeMemberId([Material] Result: [0MATERIAL].[000000000001024362] Option 2 =Text.Replace(Text.Replace(Cube.AttributeMemberId([Material]), "[0MATERIAL].[", ""), "]", "") Result: 00000000001024362 Not sure if doing the replace when adding the custom column is better or worse then creating a new replace values step but this allows me to replace the whole string in 1 step as opposed to 2 steps.
- This has been planned for a while, do we have any update?
