cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

Help with a large database and summarising columns

Hello

Would very much appreciate any assistance that you could provide on how to solve a problem that I can't.  Am not experienced with PowerBI but have built something that imports daily usage files from our product for the last 5 years.  Each file (see example Usage File layout below) contains rows for each customer and each column is a feature that customers can use that we can charge for.  Numbers in the cells are the usage for that specific customer that date.

 

DateCustomerNameTransactionsProduct 1Product 2Product 3Product 4Product 5Product 6Product 7etc
31/07/2019 00:00Customer 1000000100
31/07/2019 00:00Customer 24500000000
31/07/2019 00:00Customer 3161906816330000305
31/07/2019 00:00Customer 410000017350
31/07/2019 00:00Customer 5000000560
31/07/2019 00:00Customer 6600000000
31/07/2019 00:00Customer 7000000200
31/07/2019 00:00Customer 898001004134340
31/07/2019 00:00Customer 90000007210

 

(Each FIle I load contains about 18,000 rows and there's some 200 products - the Example Usage Layout above is very cut down to represent shape)

 

I've imported the data for counts of transactions per day ("Transactions") and have used that very easily comparing numbers of 'things used' over time.

 

I'm now trying to get to the product usage (The Product 1/2/3/4 etc Columns) - initially just to get the 'by product' usage.

 

Ideally I want to turn the data that I have already loaded (as I already include all the columns in my load) into something like the below which will make it very easy to manipulate, filter, group by dates etc

 

DateProductSum
31/07/2019 00:00Product 10
31/07/2019 00:00Product 2681
31/07/2019 00:00Product 3634
31/07/2019 00:00Product 4360
31/07/2019 00:00Product 5415
31/07/2019 00:00Product 63049
31/07/2019 00:00Product 72798
31/07/2019 00:00Product 8306
31/07/2019 00:00Product 9305
31/07/2019 00:00Product 10305
31/07/2019 00:00Product 11306
31/07/2019 00:00Product 1223
31/07/2019 00:00Product 13322
31/07/2019 00:00Product 14977
31/07/2019 00:00Product 152442
31/07/2019 00:00Product 16135
31/07/2019 00:00Product 17633

 

I beleive that this could be done by unpivoting the data and then 'grouping by' date but will so many millions of rows of data i run out of time (refreshing from sharepoint) or out of memory (manually refreshing) or out of patience (when I get errors advisinig that 'cannot convert zzzzzz (text field) to number - which implies I have a single error in the data somewhere)

 

Was wondering if there was a way of, when loading all the data, I could at the same time summarise it?

 

Sorry - this is a rambling request.  Not sure if there are several items here that need to be addressed but could anyone ask questions which might lead me to find where the issue(s) is(are)?

 

3 REPLIES 3
Highlighted
Community Support
Community Support

Re: Help with a large database and summarising columns

Hi @WalkerGBG 

Based on my knowledge, it is impossible to summarise data when loading all the data.

You could use parameter to filter data when importing data.

Power BI Desktop Query Parameters, Part 1

 

When you unpivot table or group by data, is there any error message?

 

Best Regards
Maggie

 

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Highlighted
Frequent Visitor

Re: Help with a large database and summarising columns

Not sure as no experience with PowerBI really how Parameters could work.

 

Regarding the process I've followed

When I unpivot the columns from the table - that works and even though it's doing lots and lots of columns seems to work very quickly against my large datasource (completed in a minute or two)

 

When I then do Group By it takes about 40 minutes to run and then comes up with this error:

DataFormat.Error.  We couldn't convert to Number

Details:

kmtlw.x.y

 

So looks like (I think) somewhere in my several thousand files that there's a text field in my data where it should be number

 

In the query that created this grouping "= Table.Group(#"Unpivoted Columns", {"Date1", "Attribute"}, {{"SumProduct", each List.Sum([Value]), type number}})"

Is there anyway to make it so that it nulls or ignores numbers (so handles these exceptions)?

Highlighted
Frequent Visitor

Re: Help with a large database and summarising columns

Apologies - I meant to say thank you for replying.  Appreciate you looking at this

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Find your favorite faces from the community presenting at the Power Platform Community Conference!

Upcoming Events

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors