How to design properly a set of data

[gpiero]

I have a doubt I would like to clarify before starting. It is very likely my doubt arose due I am a beginner.

I have two table: one come from ERP and it is automatically refreshed each week. Let us say that it is a dynamic table. No problem at all to connect and get the new rows.

The second one it is only a list of material, a sort of picture taken last dd/mm/yyyy and it is static. It will never changed. I can load it from an xls list, but once put in the model I would erase any source info. I do not want to keep my file xls for next 5 years.

= Excel.Workbook(File.Contents("D:\aa\2013.xlsx"), null, true)

How can I get this result?

[Greg_Deckler]

For static information, you might want to consider an "Enter Data" query and just copy your Excel workbook contents into the Enter Data editor.

[gpiero]

@Greg_Deckler

Thanks for your kind reply.

Let me check If I have understood well.

Do you mean in this way?

Copy and paste of the xls column?

[Greg_Deckler]

Basically. Open up the Excel and select all of the cells, including the "header" that you want to paste. Then go to Enter Data and select the column header for "Column1" so that the cell is highlighted. Paste. Should paste all of the data that you have copied in Excel. Then just name the query and save and close.

[gpiero]

@Greg_Deckler

Thanks, I've tried but unfortunatly does not work.

I have 18K rows to cut and paste, but I have  got a message error: the clipboard does not manage so many rows.

Then I tried to paste 2k rows each time, but after 6k rows warning again.

Could you give me another solution?

I have really go ahead  with this project.

Thanks in advance for your support

P.S. the other table contains more than 100k rows but it comes from SAP and I do not see any problem

[Greg_Deckler · Accepted solution]

What if you broke it into broke it into 4, 5,000 row Enter Data queries and then Appended them all together?

Sorry, not very good options here, this is an unusual but understandable request. I think that the real, correct answer is for the Power BI team to allow us to choose which data sources refesh and which ones do not. I would post that as an Idea if it does not already exist.

[gpiero]

Ok thanks

To be honest I did not realize I could solve appending more tables.

Done. It is not a user friendly but it works

Thanks again