Search code examples
powerquery

Is it possible to transpose a table when the number of items in each column varies and are unrelated to what is in other columns?

Is there a way to transpose a two column table so that the values in one column are the new column names and the values in the second column are listed under them in ascending order.

enter image description here

I have tried pivoting and transpose in Power Query but I either get errors or blank cells where I don't want them. I would like the values listed in ascending order from the top of each column with all the blanks to be at the bottom.

If I use pivot with count it works fine to count the number of values under each heading, but if I try to pivot with "Don't aggregate", I get an error. After researching that more I realized why I was getting an error, but couldn't figure out how to do it differently to get the results I was looking for.

I tried Transpose, merging columns with same headings, and then splitting the merged cells into rows, but the other columns duplicate what was in their columns for their values and it made a big mess with new rows for each column that was merged.

Maybe there isn't an easy solution with Power query and I would be better off using lookup array functions to populate my table, but I would like to easily refresh the data when things change so was hoping there was an easy solution in Power Query.

Edit: I tried grouping, expanding the values and then pivoting with the Power Query tools as suggested instead of pasting the given code into the advanced editor, but I got an error. My expand code didn't match what was suggested so maybe it isn't possible with the tools.

If I then pivot it, I get an error message.

If I pivot it without expanding, I get the correct columns with Table listed below. but I can't figure out how to expand the tables.
enter image description here

If I copy the code exactly as it was suggested below into the advanced editor using my fake data, it works, but I couldn't get similar to code to work on my actual data because there was a bunch of cleaning steps ahead of this step. I wasn't sure where to paste the code.

I ended using the power query grouping tool to see where it added the code in the advanced editor. I was then able paste the given code (from the grouping part down) into the advanced editor in the correct location and adjust the names as needed.

Solution

  • Yes, when you try to pivot with no aggregation, you will get errors if there are multiple entries for each "name".

    There are several solutions. The simplest for a beginner is to use the Table Group function, then construct a pivoted table from the result:

    let

//Change next line to reflect actual data source
    Source = Excel.CurrentWorkbook(){[Name="Table10"]}[Content],
    
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"column Name", type text}, {"Values", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"column Name"}, {
        {"Values", each [Values], type {text}}}),
    #"Expand Values" = Table.FromColumns(#"Grouped Rows"[Values], #"Grouped Rows"[column Name])
in
    #"Expand Values"

    enter image description here