Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
Date | CustomerName | Transactions | Product 1 | Product 2 | Product 3 | Product 4 | Product 5 | Product 6 | Product 7 | etc |
31/07/2019 00:00 | Customer 1 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | 0 | 0 |
31/07/2019 00:00 | Customer 2 | 45 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
31/07/2019 00:00 | Customer 3 | 1619 | 0 | 681 | 633 | 0 | 0 | 0 | 0 | 305 |
31/07/2019 00:00 | Customer 4 | 1 | 0 | 0 | 0 | 0 | 0 | 17 | 35 | 0 |
31/07/2019 00:00 | Customer 5 | 0 | 0 | 0 | 0 | 0 | 0 | 5 | 6 | 0 |
31/07/2019 00:00 | Customer 6 | 6 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
31/07/2019 00:00 | Customer 7 | 0 | 0 | 0 | 0 | 0 | 0 | 2 | 0 | 0 |
31/07/2019 00:00 | Customer 8 | 98 | 0 | 0 | 1 | 0 | 0 | 413 | 434 | 0 |
31/07/2019 00:00 | Customer 9 | 0 | 0 | 0 | 0 | 0 | 0 | 7 | 21 | 0 |
(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
Date | Product | Sum |
31/07/2019 00:00 | Product 1 | 0 |
31/07/2019 00:00 | Product 2 | 681 |
31/07/2019 00:00 | Product 3 | 634 |
31/07/2019 00:00 | Product 4 | 360 |
31/07/2019 00:00 | Product 5 | 415 |
31/07/2019 00:00 | Product 6 | 3049 |
31/07/2019 00:00 | Product 7 | 2798 |
31/07/2019 00:00 | Product 8 | 306 |
31/07/2019 00:00 | Product 9 | 305 |
31/07/2019 00:00 | Product 10 | 305 |
31/07/2019 00:00 | Product 11 | 306 |
31/07/2019 00:00 | Product 12 | 23 |
31/07/2019 00:00 | Product 13 | 322 |
31/07/2019 00:00 | Product 14 | 977 |
31/07/2019 00:00 | Product 15 | 2442 |
31/07/2019 00:00 | Product 16 | 135 |
31/07/2019 00:00 | Product 17 | 633 |
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)?
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.
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)?
Apologies - I meant to say thank you for replying. Appreciate you looking at this
User | Count |
---|---|
125 | |
106 | |
99 | |
63 | |
62 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |