Stem and Leaf visualization

[duncanwil]

I know I can create a stem and leaf diagram in Excel using manual methods and using a few of Excel's built in functions, including a pivot table. I have done that already.

Can anyone show me how I can create a stem and leaf diagram using Power Query only??

I would be truly gratefull!

Duncan

[duncanwil]

Thank you, Farhad.

I can take the first two steps without a hitch but when I try the Pivot Columns function, I get unusual results

I brought in my data, all two digit numbers

I created a cuplicate of the data columns and then split the new column by using 1 as the number characters as the delimiter . That works

I now have three columns : data, stem, leaf

When I pivot the table according to your instructions, line one is fine, line two is fine but then line three returns a two digit number in the leaf column rather than a one digit number in columns three and then four ...

Please review my screenshot to see what is wrong ...

Many thanks again

Duncan

[farhadidrees]

To create a stem and leaf diagram using Power Query, you will first need to import your data into Power Query. You can do this by using the "From Table/Range" option in the "Get & Transform Data" section of the "Data" tab in the ribbon.

Once your data is imported, you can use the "Split Column" function to split the values in your data column into two separate columns: one for the stem and one for the leaf. To do this, select the column you want to split and click "Split Column" in the "Transform" tab of the ribbon. Then, choose the "By Delimiter" option and specify the delimiter you want to use to split the values (for example, you could use the decimal point if your data consists of decimal numbers).

Next, you can use the "Pivot Column" function to create a pivot table based on the stem column. To do this, select the stem column and click "Pivot Column" in the "Transform" tab of the ribbon. In the "Pivot Column" window, choose the leaf column as the "Values Column" and click "OK".

Finally, you can use the "Unpivot Columns" function to convert the pivot table back into a stem and leaf diagram. To do this, select the pivot table and click "Unpivot Columns" in the "Transform" tab of the ribbon. In the "Unpivot Columns" window, choose the stem column as the "Attribute Column" and click "OK". This will convert the pivot table back into a stem and leaf diagram, with the stem values in one column and the leaf values in another.

Thanks,

Farhad 

Remember me in your prayres.

https://pk.linkedin.com/in/farhadidrees