Forum Discussion
3CX Report Import
I don't know 3CX specifically, so whether you can directly get the data from them I wouldn't be able to comment. That would entirely depend where the data is stored and what access you have.
Power BI can check back with a source either Daily or Weekly.
Getting it to remove the Top X rows is easy, as i described. Getting rid of the last row could be done by using the "Filter Rows" option and using whatever is unique about that last row to remove it. Something like a column where it is always null, but not null in your data. Or if its labeled something like "Grand Total", you could filter out specifically by that.
In the menu ribbon, click on the option "Edit Queries". In here you will find the queries, and their data import steps, before they become tables in your data model.
Normally when you bring in an excel document a few steps are done for you, based on the common import steps most people would need to take.
When you use your Get Date to bring in your 3CX report, you might have to remove any problematic steps, such as promote headers, if they occured to early.
There is also an option called "Remove Top X Rows". Here you'll need to specify how many rows to remove. Assuming those junk rows are always going to be the same number, this will work great!
Failing that, if this header block is different every time, you could find a column that should always have data and do a Filter and remove blank rows. This should achieve the same goal.
Hi Ross,
I want to import that sheet monthly. Also I need to remove top five rows and last row in that sheet.Is there any way to directly get data from 3CX to Power BI?
I don't know 3CX specifically, so whether you can directly get the data from them I wouldn't be able to comment. That would entirely depend where the data is stored and what access you have.
Power BI can check back with a source either Daily or Weekly.
Getting it to remove the Top X rows is easy, as i described. Getting rid of the last row could be done by using the "Filter Rows" option and using whatever is unique about that last row to remove it. Something like a column where it is always null, but not null in your data. Or if its labeled something like "Grand Total", you could filter out specifically by that.
Thank You so much for your help