Basic report/query building

[nunnc01]

Hi all,

I think I must be missing something obvious but if I have connected to my data tables and added the relationships, how do I then just bring together the relevant columns from my tables into one data view. For example in crystal reports and Access you get the data and build the relationships and then you create the query/report by simply selecting your chosen fields and adding calculated fields if required.

I do not need to create visuals in this example, just want to build a data report that I can then export into Excel.

Can anyone offer any advice please?

[v-jiascu-msft]

Hi @nunnc01,

Could you please mark the proper answers as solutions?

Best Regards,

Dale

[Anonymous]

Did your bring in the tables and created the relationships? That's usually the big hurdle as BI requires one of the tables to have a distinct field to connect to. 

ONe they are connected you simply pull the fieldsa into a table. You do not have to see all the fields in BI if there are many. 

Then export to .csv. To export, click on the frame of the Visiual/table, in the upper right corner of the visual you will see three dots. Click on the dots and export. 

If there is too much data (too many rows) you will need to filter and export in sections. 

Is that all you needed? 

[nunnc01]

When you say pull them into a table, do you mean the table visualisation or an actual table in the edit query section?

The export row limitation is a big problem. Surely there must be a way of exporting all the rows from my results??

[Anonymous]

I meant table visualization and you are limited to the rows that can be exporeted. You should be able to find some means to group them: by Month etc. Otherwise, export using date filters. 

Since you are using BI only as a query tool, something it does but is not designed for, I suggest using Power Pivot. 

[v-jiascu-msft]

Hi @nunnc01,

It's the Table visualization. Why not connecting to the data source directly? What's the data source?

Best Regards,

Dale

[nunnc01]

I have connected to my SQL database and selected the 4 tables I need. All 4 tables show in the Data section of Power BI.

I have then gone into the relationship screen and joined the 4 tables.

What I now want to do is select a few columns from each of the 4 tables and create a new table showing just those. I have tried to use the table visualisation but this limits how many records I can export.

I need to be able to export the full dataset into excel.

Any advice would be greatly appreciated.

Thanks 

[Anonymous]

Use Power Pivot and that will place the data directly into Excel. The data query is the same as BI. 

[v-jiascu-msft]

Hi @nunnc01,

I'm afraid the limits are hard to change for now. It seems you only want to get data in Excel. I would introduce you a workaround.

1. Create a view in the database, which contains all the columns you need. 

2. Connect to the database in the Excel and retrieve data from that view. 

3. Put the data in a table. 

Best Regards,

Dale