Power BI desktop SAP BW connector error

Question

HI allHave a customer, who want to use PBI with SAP BW connector(2.0). When connecting, I see all the requireg atributes in the SAP query. I can load to PBI some dimension/hierarchies (using the import). But when I add key featrues and go to transform data, i see error Expression.Error: The key didn't match any rows in the table. In the details is some ID=[Measure][], key= (there is nothing) and the Table=[table](which is the name of created table). When I close and aply the data tranform, it looks like, that all the data are loaded (all key features and hierarchies...). Without error.When importing with directQuery, eveything is without error.Do someone what is wrong?Any advice will be helpfullThank youtomas

v-jingzhang · Answer

Hi&nbsp;tomas12344&nbsp;
&nbsp;
Chris Webb has a blog about this error. You may find it helpful:&nbsp;Understanding The “The key didn’t match any rows in the table” Error In Power Query In Power BI Or Excel
&nbsp;
Best Regards, Community Support Team _ JingIf this post helps, please Accept it as Solution to help other members find it.

tomas12344 · Answer

Thanks for the answer, but the blog is about excel. i have nothig written about excel, but about SAP BW.

v-jingzhang · Answer

Hi&nbsp;tomas12344&nbsp;
&nbsp;
Can you share the M codes from its Advanced Editor? Please remove sensitive info before sharing. And this error starts to appear in which step?&nbsp;
&nbsp;
Jing

tomas12344 · Answer

HI JingThank you again. Attached is the source code from PBI. the problem was with the measure&nbsp;Amount in LC,which is currently the last added in the list. I dont know, what was wrong. I have opened the advanced editor, to try to find something, there was no error (marked, ...). I have removed the measure, which was mentioned in the error. Then it worked fine. then i added the measure back, and since then i dont get any error. The only difference is, that the measure was original first, now it is last (i dont think, that i can change the order when selecting from the cube).&nbsp;let
    Source = SapBusinessWarehouse.Cubes("sapbi1.xxxx.xxxxx", "20", "020", [Implementation="2.0"]),
    HVF17SI01 = Source{[Name="HVF17SI01"]}[Data],
    #"HVF17SI01/HV17_FIN_HVF17SI01_A0002_PBI1" = HVF17SI01{[Id="HVF17SI01/HV17_FIN_HVF17SI01_A0002_PBI1"]}[Data],
    #"Added Items" = Cube.Transform(#"HVF17SI01/HV17_FIN_HVF17SI01_A0002_PBI1",
        {
            {Cube.AddAndExpandDimensionColumn, "[0ACCOUNT__YTACCOUNT]", {"[0ACCOUNT__YTACCOUNT           YHIE_T_ZCHFRPB_AC_DBN_NL].[LEVEL00]", "[0ACCOUNT__YTACCOUNT           YHIE_T_ZCHFRPB_AC_DBN_NL].[LEVEL01]", "[0ACCOUNT__YTACCOUNT           YHIE_T_ZCHFRPB_AC_DBN_NL].[LEVEL02]", "[0ACCOUNT__YTACCOUNT           YHIE_T_ZCHFRPB_AC_DBN_NL].[LEVEL03]", "[0ACCOUNT__YTACCOUNT           YHIE_T_ZCHFRPB_AC_DBN_NL].[LEVEL04]", "[0ACCOUNT__YTACCOUNT           YHIE_T_ZCHFRPB_AC_DBN_NL].[LEVEL05]", "[0ACCOUNT__YTACCOUNT           YHIE_T_ZCHFRPB_AC_DBN_NL].[LEVEL06]", "[0ACCOUNT__YTACCOUNT           YHIE_T_ZCHFRPB_AC_DBN_NL].[LEVEL07]"}, {"0ACCOUNT__YTACCOUNT.Level 01", "0ACCOUNT__YTACCOUNT.Level 02", "0ACCOUNT__YTACCOUNT.Level 03", "0ACCOUNT__YTACCOUNT.Level 04", "0ACCOUNT__YTACCOUNT.Level 05", "0ACCOUNT__YTACCOUNT.Level 06", "0ACCOUNT__YTACCOUNT.Level 07", "0ACCOUNT__YTACCOUNT.Level 08"}},
            {Table.AddColumn, "0ACCOUNT__YTACCOUNT.Level 01.0ACCOUNT__YTACCOUNT Level 01.UniqueName", each Cube.AttributeMemberProperty([0ACCOUNT__YTACCOUNT.Level 01], "[MEMBER_UNIQUE_NAME]")},
            {Table.AddColumn, "0ACCOUNT__YTACCOUNT.Level 02.0ACCOUNT__YTACCOUNT Level 01.UniqueName", each Cube.AttributeMemberProperty([0ACCOUNT__YTACCOUNT.Level 02], "[MEMBER_UNIQUE_NAME]")},
            {Table.AddColumn, "0ACCOUNT__YTACCOUNT.Level 03.0ACCOUNT__YTACCOUNT Level 01.UniqueName", each Cube.AttributeMemberProperty([0ACCOUNT__YTACCOUNT.Level 03], "[MEMBER_UNIQUE_NAME]")},
            {Table.AddColumn, "0ACCOUNT__YTACCOUNT.Level 04.0ACCOUNT__YTACCOUNT Level 01.UniqueName", each Cube.AttributeMemberProperty([0ACCOUNT__YTACCOUNT.Level 04], "[MEMBER_UNIQUE_NAME]")},
            {Table.AddColumn, "0ACCOUNT__YTACCOUNT.Level 05.0ACCOUNT__YTACCOUNT Level 01.UniqueName", each Cube.AttributeMemberProperty([0ACCOUNT__YTACCOUNT.Level 05], "[MEMBER_UNIQUE_NAME]")},
            {Table.AddColumn, "0ACCOUNT__YTACCOUNT.Level 06.0ACCOUNT__YTACCOUNT Level 01.UniqueName", each Cube.AttributeMemberProperty([0ACCOUNT__YTACCOUNT.Level 06], "[MEMBER_UNIQUE_NAME]")},
            {Table.AddColumn, "0ACCOUNT__YTACCOUNT.Level 07.0ACCOUNT__YTACCOUNT Level 01.UniqueName", each Cube.AttributeMemberProperty([0ACCOUNT__YTACCOUNT.Level 07], "[MEMBER_UNIQUE_NAME]")},
            {Table.AddColumn, "0ACCOUNT__YTACCOUNT.Level 08.0ACCOUNT__YTACCOUNT Level 01.UniqueName", each Cube.AttributeMemberProperty([0ACCOUNT__YTACCOUNT.Level 08], "[MEMBER_UNIQUE_NAME]")},
            {Cube.AddAndExpandDimensionColumn, "[HRRLDNR]", {"[HRRLDNR].[LEVEL01]"}, {"Ledger.Ledger Level 01"}},
            {Table.AddColumn, "Ledger.Ledger Level 01.Key", each Cube.AttributeMemberProperty([Ledger.Ledger Level 01], "[2HRRLDNR]")},
            {Table.AddColumn, "Ledger.Ledger Level 01.Ledger Level 01.UniqueName", each Cube.AttributeMemberProperty([Ledger.Ledger Level 01], "[MEMBER_UNIQUE_NAME]")},
            {Cube.AddAndExpandDimensionColumn, "[HRYYLOB__HRYYLOB1]", {"[HRYYLOB__HRYYLOB1             YHIE_C_ZCHFRPB_NL_RO_ULOB].[LEVEL00]", "[HRYYLOB__HRYYLOB1             YHIE_C_ZCHFRPB_NL_RO_ULOB].[LEVEL01]", "[HRYYLOB__HRYYLOB1             YHIE_C_ZCHFRPB_NL_RO_ULOB].[LEVEL02]", "[HRYYLOB__HRYYLOB1             YHIE_C_ZCHFRPB_NL_RO_ULOB].[LEVEL03]"}, {"Line of Business.Level 01", "Line of Business.Level 02", "Line of Business.Level 03", "Line of Business.Level 04"}},
            {Table.AddColumn, "Line of Business.Level 01.Key", each Cube.AttributeMemberProperty([Line of Business.Level 01], "[2HRYYLOB__HRYYLOB1]")},
            {Table.AddColumn, "Line of Business.Level 01.Line of Business Level 01.UniqueName", each Cube.AttributeMemberProperty([Line of Business.Level 01], "[MEMBER_UNIQUE_NAME]")},
            {Table.AddColumn, "Line of Business.Level 02.Key", each Cube.AttributeMemberProperty([Line of Business.Level 02], "[2HRYYLOB__HRYYLOB1]")},
            {Table.AddColumn, "Line of Business.Level 02.Line of Business Level 01.UniqueName", each Cube.AttributeMemberProperty([Line of Business.Level 02], "[MEMBER_UNIQUE_NAME]")},
            {Table.AddColumn, "Line of Business.Level 03.Key", each Cube.AttributeMemberProperty([Line of Business.Level 03], "[2HRYYLOB__HRYYLOB1]")},
            {Table.AddColumn, "Line of Business.Level 03.Line of Business Level 01.UniqueName", each Cube.AttributeMemberProperty([Line of Business.Level 03], "[MEMBER_UNIQUE_NAME]")},
            {Table.AddColumn, "Line of Business.Level 04.Key", each Cube.AttributeMemberProperty([Line of Business.Level 04], "[2HRYYLOB__HRYYLOB1]")},
            {Table.AddColumn, "Line of Business.Level 04.Line of Business Level 01.UniqueName", each Cube.AttributeMemberProperty([Line of Business.Level 04], "[MEMBER_UNIQUE_NAME]")},
            {Cube.AddAndExpandDimensionColumn, "[HRBUDAT]", {"[HRBUDAT].[LEVEL01]"}, {"Posting Date.Posting Date Level 01"}},
            {Table.AddColumn, "Posting Date.Posting Date Level 01.Key", each Cube.AttributeMemberProperty([Posting Date.Posting Date Level 01], "[2HRBUDAT]")},
            {Cube.AddAndExpandDimensionColumn, "[HRYYTOB]", {"[HRYYTOB].[LEVEL01]"}, {"Type of business.Type of business Level 01"}},
            {Table.AddColumn, "Type of business.Type of business Level 01.Key", each Cube.AttributeMemberProperty([Type of business.Type of business Level 01], "[2HRYYTOB]")},
            {Table.AddColumn, "Type of business.Type of business Level 01.Type of business (Key)", each Cube.AttributeMemberProperty([Type of business.Type of business Level 01], "[2HRYYTOB1]")},
            {Table.AddColumn, "Type of business.Type of business Level 01.Type of business (Name)", each Cube.AttributeMemberProperty([Type of business.Type of business Level 01], "[1HRYYTOB1]")},
            {Table.AddColumn, "Type of business.Type of business Level 01.Type of business Level 01.UniqueName", each Cube.AttributeMemberProperty([Type of business.Type of business Level 01], "[MEMBER_UNIQUE_NAME]")},
            {Cube.AddMeasureColumn, "Amount in LC", "[Measures].[003YQUCE1YCOU8YD4B7C3MKAW]"}
        }),
    #"Removed Columns" = Table.RemoveColumns(#"Added Items",{"Type of business.Type of business Level 01.Type of business Level 01.UniqueName", "Type of business.Type of business Level 01.Type of business (Name)", "Type of business.Type of business Level 01.Key"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Removed Columns",{{"Posting Date.Posting Date Level 01.Key", type date}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Ledger.Ledger Level 01", "Ledger.Ledger"}, {"Posting Date.Posting Date Level 01.Key", "Posting Date"}, {"Type of business.Type of business Level 01", "Type of business"}, {"Type of business.Type of business Level 01.Type of business (Key)", "Type of business (Key)"}})
in
    #"Renamed Columns"&nbsp;

Forum Discussion

Power BI desktop SAP BW connector error

4 Replies

Related content

Power BI Desktop - Snowflake connector

Power BI Connector Certification

Adobe Analytics connector OAuth failure in Power BI Desktop (CSRF / state mismatch)

Power BI Modeling MCP with OpenAI Codex Desktop: connection to Power BI Desktop and Service

Deploying a custom connector to Power BI Desktop