SAP BW hierarchy + properties via gateway: Different data shown in Dataflow vs. Power Query

[mattwall]

Dear all,

we are facing a strange problem when loading a cost center hierarchy + additional properties from SAP BW via our local gateway:

Power Query: When loading the hierarchy in Power Query (import mode) via our local Power BI gateway, the hierarchy gets loaded as it should.

Dataflow: However, when copy-pasting the M-Code from Advanced Editor into a blank Dataflow query within Power BI Service, using also the local gateway, the cost center ID is loaded instead of the costcenter name. This is the same for all different properties selected.

Could somebody help here?

Thanks and best wishes,

Matthias

[mattwall]

Hi @v-heq-msft,

thanks for getting back to me! Here is the M-Code that I copy-pasted from Power Query into the Dataflow:

let
    Source = SapBusinessWarehouse.Cubes("xxx.xxx.local", "02", "100", [Implementation="2.0"]),
    #"0COSTCENTER" = Source{[Name="0COSTCENTER"]}[Data],
    #"0COSTCENTER/ZCOSTCENTER" = #"0COSTCENTER"{[Id="0COSTCENTER/ZCOSTCENTER"]}[Data],
    #"Added Items" = Cube.Transform(#"0COSTCENTER/ZCOSTCENTER",
        {
            {Cube.ApplyParameter, "[!V000001]", {{"[0COMP_CODE].[0010]"}}},
            {Cube.AddAndExpandDimensionColumn, "[0COSTCENTER]", {"[0COSTCENTER                   1000PT1].[LEVEL05]"}, {"Kostenstelle.Stufe 06"}},
            {Table.AddColumn, "Kostenstelle.Stufe 06.Bezeichnung", each Cube.AttributeMemberProperty([Kostenstelle.Stufe 06], "[10COSTCENTER]")},
            {Table.AddColumn, "Kostenstelle.Stufe 06.Bezeichnung mittel", each Cube.AttributeMemberProperty([Kostenstelle.Stufe 06], "[50COSTCENTER]")},
            {Table.AddColumn, "Kostenstelle.Stufe 06.O.Währung (Schlüssel)", each Cube.AttributeMemberProperty([Kostenstelle.Stufe 06], "[20OBJ_CURR]")}
        })
in
    #"Added Items"

The results look as follows:

Power Query (correct):

Dataflow (incorrect):

It looks as if the SAP BW query is not implemented identically in Power Query vs. Dataflow. Furthermore, I realized that just the cost center level 06 (the first column) and properties showing the name of the data field in different lengths (col 2 and 3 here) do not show the correct data, all other properties selected work fine, such as col 4. It looks as if the cost center ID (e.g. 1000/100000000) is not resolved into the cost center name correctly in the Dataflow query.

Does this help in the first step or do you need some more information?

Best,

Matthias

[v-heq-msft]

Hi  @mattwall ,

To troubleshoot the problem further, please provide the contents of the M-code. 

I'm not sure if you need to show the same results in dataflow as in Desktop, please provide your desired results in excel or screenshots.

Best regards

Albert He

[mattwall]

Hi @v-heq-msft ,

did you already take a closer look at the above issue? Please see the additional details I delivered, as you requested.

Best,

Matthias

[Elnie]

Try switching Service to a local language (German?).I solved this recently and it is caused by the language dependency enabled in SAP and then in BW. If you have Service in English, it looks at the unfilled text version and puts the ID there.

[mattwall · Accepted solution]

Hi @Elnie ,

I figured out the solution using your hint towards language settings: I needed to set the language code to "de" (= German) in the data source settings, that's it.

Many thanks & best wishes,

Matthias

[mattwall]

Hi @Elnie ,

thanks for your reply. I tried switching the Power BI Service (I guess you meant this service, right) to German, but unfortunately it still does not work. Did you try turning off language dependency in SAP?

Best, Matthias