How to handle repeated groups of columns?

[pie333]

Dear all, I just started to learn data analysis and visualization so forgive me if my questions sound stupid 🙂

My dataset is coming from a survey where farmers, among other questions, were asked to tell what kind of crops were they growing in their fields.

I'd like to know:

- What is the best way to handle this kind of data? What are the options?

- How to use power bi to show them in a meaningful way?

For example I'd like to filter every farmer growing apples, what are the crops being grown in one specific area (not in my sample data, but the info exists in the full dataset), know how much free/non cultivated land they still have, how much income they are making with one or more crops... everything that it's possible to know using these data.

My original file is under NDA so I made a sample stripped fake dataset showing how the repeated groups are structured. I know that one column is missing in the last repeated group (plot C workers), and that is on purpose because some columns are missing in my original dataset too (don't know why, but that's it).

The repeated groups are identified through plot A, plot B, plot C prefixes in the column headings: there is no other meaning there.

farmer	total land (ac)	cultivated surface	plot A cultivated surface (ac)	plot A cultivated plant	plot A workers	plot A yield in kg	plot A expenses (workers wages, rent, consumables)	plot A selling price per kg	plot A yield expected value	plot B cultivated surface (ac)	plot B cultivated plant	plot B workers	plot B yield in kg	plot B expenses (workers wages, rent, consumables)	plot B selling price per kg	plot B yield expected value	plot C cultivated surface (ac)	plot C cultivated plant	plot C yield in kg	plot C expenses (workers wages, rent, consumables)	plot C selling price per kg	plot C yield expected value
George	12	6	2	Coffee	2	120	1 200 €	15 €	1 800 €	3	Banana	1	300	700 €	3 €	900 €	1.0	Tea	60	600 €	25.0 €	1 500 €
Mark	8	5.5	1	Orange	0	50	100 €	1.50 €	75 €	2	Apple	2	750	400 €	1.5	1 125 €	2.5	Coffee	100	160 €	10.0 €	1 000 €
Anna	23	22	22	Banana	3	1230	1 800 €	8	9 840 €	0	0	0	0	-   €	0	0 €	0	0	0	-   €	-   €	0 €
Yuri	6	5.5	3	Avocado	1	400	2 000 €	6	2 400 €	2.5	Strawberry	4	90	500 €	10	900 €	0	0	0	-   €	-   €	0 €

Any help or suggestion is appreciated since I have no clue on how to proceed here... 🙂

Thank you in advance

[Anonymous · Accepted solution]

Hi @pie333 ,

It's suggested to use Matrix visual after doing some transform in Power Query(like Split, Unpivot...):

Or use Pivot based on the steps below:

Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[pie333]

Thank you this is priceless!!

If I may ask for a further step on this same topic: is there any chance to extract the pivoted table and feed it/link it through table relations?

Following your steps every uniqueID now appears 3 times (with different values only in the pivoted columns) and I have other repeated groups to handle: I will end up with a table of 3000+ entries (most of which are the same values) from the original 600 records/rows.

It might be much more polished if I could split my big table in many smallest tables each one containing a "selection of columns" generated from the repeated groups in my dataset:

ie. One table containing every "single time" info, linked through uniqueID to x other tables which contains only the data from my repeated groups (one would be this "crop section").

[edit]

I noticed that having all of these multiplicated records leads me to a new layer of complexity: as an example if I want to calculate the $ value of each yield inserting a column that counts current $price*kg I can't just use SUMX, but I need to CALCULATE(SUMX(...) and add filters on ID and plant type (link for reference if someone else stumbles here with the same issue https://community.powerbi.com/t5/Desktop/Sumx-to-sum-values-for-each-unique-ID-in-row/m-p/367142) .

As another example, unless I did something wrong on the visualization window, if I add a column to regroup the people in groups based on expected income (Poor, Midclass, Rich - here a link on how I made my new column and for future reference https://community.powerbi.com/t5/Desktop/Grouping-in-a-Pie-Chart/m-p/405490 ) and show it in a piechart everyone is counted multiple times.

While this might be not influent showing values in % it is if I need to show the distinct count.. what are my options?

I noticed that this second issue happens because I have different "incomes" per person (one from each plant) and I am not putting them together 😓 my fault: I need to learn how to do that too.

I guess that if I put all my plants info in an external referenced table all of this shouldn't happen.

Am I right?

Thank you again! 🙂