appended query is loaded with too little rows

[Anonymous]

Hello,

I have the following problem:

I load multiple queries per country from SAP Business Warehouse and create tables with thousands of rows. Then I append them alltogether as a new table to have a consolidation absolutely necessary to build a report.

However, when I upload appended query from Power Query to Power BI itself it consists only of 15,000 rows which is incorrect. Some of appended tables have 200,000 rows, no way that the consolidation would consist of 15,000 rows only.

I suspect that instead of appending whole tables the query appends only the data previews which usually consist of +/- 1,000 rows; hence, that's why the appended consolidation has only 15,000 rows, because it consists more or less of 15 previews.

Hopefully, I made myself clear; let me know in case of any questions 🙂

Thank you in advance. 

[ToddChitt · Accepted solution]

Curious if the data source being a Cube has anything to do with it.

Try this in DAX:

My UNION Table = UNION ('Inv TR, 'Inv ES', ...)

[ToddChitt]

What happens if you load each of the source tables into the model instead of doing the Append? 

Do all the tables have the exact same meta-data (columns and types)?

what happens if you try to do a UNION statement in DAX?

Can you show us your Power Query code?

[Anonymous]

Hello,

1) Yes, if I load each table separately they have proper amount of rows (from 2,000 to 200,000). All tables have the same layout, because I'm using almost the same query for each chanding only country code.

Here the query to extract data per country:

let
Source = SapBusinessWarehouse.Cubes("databasename", "00", "100", [LanguageCode="EN", Implementation="2.0", BatchSize=500000000]),
SourceName = Source{[Name="SourceName"]}[Data],
#"Added Items" = Cube.Transform(#"query name",
{
{Cube.ApplyParameter, "[CV_CMON]", {"[0CALMONTH].["& PastMonthYear & InventoryMonth &"]"}}, - here I set the month for which the data is extracted
{Cube.ApplyParameter, "[!V000007]", {{"[ST_PLANT__S_CNTRY].[AT]"}}}, - the contry code, the only thing that changes in queries which are appended

{Cube.AddAndExpandDimensionColumn, "Material Name"}},
{Table.AddColumn, "M-Material/Plant.M-Material/Plant Level 01.Key)},
{Cube.AddAndExpandDimensionColumn, "Unit of Measure"}},
{Cube.AddMeasureColumn, "Stock Qty""}
})
in
#"Added Items"

PLEASE NOTE that I changed query to not give away query, and database names.

2) I haven't tried DAX UNION never heard of this.. Should I create a new table and paste this DAX formula in it?

3) My query to append tables:

let
Source = Table.Combine({#"Inv TR", #"Inv ES", #"Inv AT", #"Inv DE",
#"Inv CZ", #"Inv FI", #"Inv BE", #"Inv IT", #"Inv SE", #"Inv FR", #"Inv PL",
#"Inv RU", #"Inv KE", #"Inv ZA", #"Inv GB", #"Inv NL"})
in
Source

Nothing fancy just the tables with the same structure for different countries.

[ToddChitt · Accepted solution]

Curious if the data source being a Cube has anything to do with it.

Try this in DAX:

My UNION Table = UNION ('Inv TR, 'Inv ES', ...)

[Anonymous · Accepted solution]

Fun fact:

I have old Power Bi version from May. After I updated it, the problem solved itself 😄 

[Anonymous]

Thank you, it worked 🙂