Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Afternoon All,
Try to keep it brief but informative, having an issue where I want to display data over time (years) but the data I have received has, for example, column 1 named [2010 Sales) then another column [2011 Sales] and there is no actual Date column.
As each row will be a distinct product or product variation then columns to display sales in 20XX and any other data associated with that product.
Whereas the usual format we would expect is the [Date] column and a single sales column, then we use time intelligence to display sales per year, month, day etc.
Wondered if anyone has come across this issue, and how they overcame it.
Already tried to get another view of the data where an actual date would be used but this is not possible, unfortunately.
Thanks,
Josh
Solved! Go to Solution.
Here's what I think you're looking for. Take a look at the steps in PowerQuery for details on how I did it. Essentially an unpivot and then a pivot.
https://github.com/ssugar/PowerBICommunity/raw/master/community-sol-575635.pbix
Could you provide some sample data?
Just off the top of my head, if you've got "2010 Sales" in one column, and "2011 Sales" in another column, you could look at unpivoting all the columns with the "#### Sales" in them, which would get you to a single column with those attributes. Then you could split the column by space, and then you'd have a year column which could be used to create an actual date column. Would need some sample data to get you a clearer answer.
So at the moment, the data is laid out like;
Tax Status | Region | Minor Region | Country | Category 1 | Category 2 | Brand | Quality | Origin | Local/Imported | Volume 2010 | Vol 2010 | Volume 2011 | Vol 2011 | Volume 2012 | Vol 2012 | Volume 2013 | Vol 2013 | Volume 2014 | Vol 2014 | Volume 2015 | Vol 2015 | Volume 2016 | Vol 2016 | Volume 2017 | Vol 2017 | USD Price 2011 | USD Price 2012 | USD Price 2013 | USD Price 2014 | USD Price 2015 | USD Price 2016 | Bottle Size 2010 | Bottle Size 2011 | Bottle Size 2012 | Bottle Size 2013 | Bottle Size 2014 | Bottle Size 2015 | Bottle Size 2016 | USD Value 2010 | USD Value 2011 | USD Value 2012 | USD Value 2013 | USD Value 2014 | USD Value 2015 | USD Value 2016 |
Domestic | Africa & Middle East | Africa | Angola | Toys | Plastics | Sega | Premium | China | Imported | 10.0000000 | 0.0100000 | 0.0000001 | 0.0000000 | 33.8987229 | 33.8987229 | 75.0000000 | 75.0000000 | 4.067846753 | 4.06785E-08 | ||||||||||||||||||||||||||
Domestic | Africa & Middle East | Africa | Angola | Toys | Plastics | Playstation | Ultra Premium | China | Imported | 10.0000000 | 0.0100000 | 10.0000000 | 0.0100000 | 0.0000001 | 0.0000000 | 86.2876584 | 86.2876584 | 86.2876584 | 75.0000000 | 75.0000000 | 75.0000000 | 10.35451901 | 10.35451901 | 1.03545E-07 | |||||||||||||||||||||
Domestic | Africa & Middle East | Africa | Angola | Toys | Plastics | Microsoft | Ultra Premium | Americas | Imported | 10.0000000 | 0.0100000 | 10.0000000 | 0.0100000 | 0.0000001 | 0.0000000 | 86.2876584 | 86.2876584 | 86.2876584 | 75.0000000 | 75.0000000 | 75.0000000 | 10.35451901 | 10.35451901 | 1.03545E-07 |
and in an ideal scenario i could do with the layout been like; (manually done for row 1);
Tax Status | Region | Minor Region | Country | Category 1 | Category 2 | Brand | Quality | Origin | Local/Imported | Date (Year) | Volume | Value | Price | Size |
Domestic | Africa & Middle East | Africa | Angola | Toys | Plastics | Sega | Premium | China | Imported | 2014 | 10.00 | |||
Domestic | Africa & Middle East | Africa | Angola | Toys | Plastics | Sega | Premium | China | Imported | 2015 | 10.00 | 4.07 | 33.89 | 75.00 |
Domestic | Africa & Middle East | Africa | Angola | Toys | Plastics | Sega | Premium | China | Imported | 2016 | 1.00 | 0.00 | 33.89 | 75.00 |
The actual source data is laid out like the example data though, does a pivot / unpivot do that... Not something ive done in PowerBI before but sounds like what i need.
Thanks again,
Josh
Here's what I think you're looking for. Take a look at the steps in PowerQuery for details on how I did it. Essentially an unpivot and then a pivot.
https://github.com/ssugar/PowerBICommunity/raw/master/community-sol-575635.pbix
Thank you very much!
Now I've seen it, it seems so simple but honestly, I was scratching my head for hours!
Thanks,
Josh
Happy I could help 🙂
Just give me 5 minutes and ill have an example data set, thank you in advanced.
User | Count |
---|---|
124 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
137 | |
116 | |
102 | |
71 | |
61 |