Merge Tables Power Query in Direct Query

[JohnnyT48]

I have a direct feed connection that will not allow file to be published if i import a CSV or Excel data table. 

I have copy and pasted data into "Enter Data" and get the 3,000 Cell Message Below:

So I split the information into two tables and tried to combine them within Power Query: This has worked but hit the limit again at 3000 cells when I merge. 

I made a table with 1500 Rows 2 Columns = 3000 Cells

Second Table I made 700 Rows 2 Columns = 1400 Cells

I have joined the tables using Power Query. Merge as New. But still have 1500 Rows and 3000 Cells....

Is there anyway around this to get 2200 Rows when I merge and 4400 cells. 

Kept trying to join and merge the two tables but cannot exceed the 1500 rows even after spiltting and merging. 

[rajendraongole1 · Accepted solution]

Hi @JohnnyT48 - When utilizing the "Enter Data" option in Power BI, there is a 3,000-cell limit that you must adhere to.

Instead of merging the tables in Power Query, you can use DAX to combine the tables within Power BI. This approach avoids the 3,000-cell limit imposed by the "Enter Data" feature.

CombinedTable =
UNION(
SELECTCOLUMNS('Table1', "Column1", 'Table1'[Column1], "Column2", 'Table1'[Column2]),
SELECTCOLUMNS('Table2', "Column1", 'Table2'[Column1], "Column2", 'Table2'[Column2])
)

or you can also check by choosing the smaller datasets as like 

Table1: 1000 rows
Table2: 1000 rows
Table3: 200 rows
Append in Power Query:

Append Table1 and Table2.
Append the result with Table3.

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[rajendraongole1 · Accepted solution]

Hi @JohnnyT48 - When utilizing the "Enter Data" option in Power BI, there is a 3,000-cell limit that you must adhere to.

Instead of merging the tables in Power Query, you can use DAX to combine the tables within Power BI. This approach avoids the 3,000-cell limit imposed by the "Enter Data" feature.

CombinedTable =
UNION(
SELECTCOLUMNS('Table1', "Column1", 'Table1'[Column1], "Column2", 'Table1'[Column2]),
SELECTCOLUMNS('Table2', "Column1", 'Table2'[Column1], "Column2", 'Table2'[Column2])
)

or you can also check by choosing the smaller datasets as like 

Table1: 1000 rows
Table2: 1000 rows
Table3: 200 rows
Append in Power Query:

Append Table1 and Table2.
Append the result with Table3.

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[JohnnyT48 · Accepted solution]

Thank you very much just tested and it is working, appreciate your help.

Thank you