none
Rearrange data into columns

    Question

  • Hi,

    My base data is shown in range C3:G7. I's like my data to appear as shown in range C11:G19.

    What kind of a query can be written for this.  Please help.

    Thank you.


    Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com

    Saturday, September 14, 2013 11:54 PM

Answers

All replies

  • Hi,

    Any help please.  I understand that two activates need to be performed to solve this problem.

    1. Unpivot the Budget and Actual columns - I can do this
    2. Show June and July as columns (Pivot/Transpose the month column) - I am stuck here.

    Please help.


    Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com

    Monday, September 16, 2013 11:48 PM
  • Hi Ashish,

    You were on the right path, but needed to use Table.Pivot and also (in case you want this query to be future-proofed) build the list of values to pivot by dynamically (i.e. distinct values in the month column).

    This query should do what you want:

    let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        Unpivot = Table.Unpivot(Source,{"Budget", "Actual"},"Attribute","Value"),
        Custom1 = Table.Pivot(Unpivot, List.Distinct(Unpivot[Month]), "Month", "Value", List.Sum),
        SortedRows = Table.Sort(Custom1,{{"July", Order.Ascending}, {"June", Order.Ascending}, {"Name", Order.Ascending}})
    in
        SortedRows
     

    1. Note 1: Adjust the Source step to point to your table.
    2. Note 2: You can get rid of the SortedRows step in case the order of the rows doesn't matter.

    Thanks,
    M.

     


    Saturday, September 21, 2013 12:26 AM
    Owner
  • Hi,

    Thank you for replying.  I tried the following query and received an error message that reads as

    Expression.Error: The name 'Table.Pivot' was not recognized.  Is it spelled correctly? Details: Name=Table.Pivot, SectionName=Section1, FormulaName=Table4, FormulaPartName=Custom1

    let
        Source = Excel.CurrentWorkbook(){[Name="Table4"]}[Content],
        Unpivot = Table.Unpivot(Source,{"       PM1", "     PM1.1", "     PM1.2", "     PM1.3", "     PM1.4", "     Expenses 1", "      Exp.1 QT1", "      Exp.1 QT2", "      Exp.1 QT3", "      Exp.1 QT4"},"Attribute","Value"),
        Custom1 = Table.Pivot(Unpivot, List.Distinct(Unpivot[Edition]), "Month", "Value", List.Sum)
    in
        Custom1
    Edition is the title of the column of my table which has months.  Where is my error?


    Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com


    Saturday, September 21, 2013 11:43 PM
  • Hi,

    I tried your exact query on my screenshot posted above and the following error message popped up

    Expression.Error: The import Table.Pivot matches no exports (did you miss a module reference?). Details: null

    Please help.


    Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com

    Sunday, September 22, 2013 8:45 AM
  • Hi Ashish,

    The function is available in our latest update (2.7.3416.121), which you can get from this page: http://www.microsoft.com/en-us/download/details.aspx?id=39933

    Thanks,
    M.

    • Marked as answer by Ashish Mathur Saturday, September 28, 2013 5:20 AM
    Monday, September 23, 2013 5:30 PM
    Owner
  • Hi,

    Thank you for all your help offered so far.  Your query is now working but the result is still not as desired.  In the file below, my desires solution is available in the desired solution sheet.  SO while the data in columns is coming correct ( I just compared the sum of all individual columns), the number of rows your solution is occupying is far more than the ones shown on my solution.

    http://sdrv.ms/15py5PS

    Please help.


    Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com

    Tuesday, September 24, 2013 2:25 PM
  • Hi Ashish,

    Can you spot the difference? Is it based on some extra groupings that need to be done?

    Thanks,
    M.

    Friday, September 27, 2013 4:03 PM
    Owner
  • It looks like the extra rows are due to the fact that the PQ output has only one month column value per row, but your desired output has only one row with all month values, as far as I can tell.

    You will need to use the function Table.FillDown after defining the right sort criteria in your rows, to populate the right values, and then remove duplicates. Something that can also be helpful is "fill up", but this isn't directly supported, you will have to instead build on Table.ReverseRows (to turn a table upside down) and Table.FillDown.

    Thanks,
    M.

    Friday, September 27, 2013 4:08 PM
    Owner
  • Hi,

    I got it.  The following query gets me the exact result as shown in the "Desired Result" sheet.

    let
        Source = Excel.CurrentWorkbook(){[Name="Table4"]}[Content],
        Unpivot = Table.Unpivot(Source,{"       PM1", "     PM1.1", "     PM1.2", "     PM1.3", "     PM1.4", "     Expenses 1", "      Exp.1 QT1", "      Exp.1 QT2", "      Exp.1 QT3", "      Exp.1 QT4"},"Attribute","Value"),
        Custom1 = Table.Pivot(Unpivot, List.Distinct(Unpivot[Edition]), "Edition", "Value", List.Sum),
        GroupedRows = Table.Group(Custom1, {"Account", "Activity", "Year", "Attribute"}, {{"May", each List.Sum([May]), type none}, {"June", each List.Sum([June]), type none}, {"July", each List.Sum([July]), type none}, {"August", each List.Sum([August]), type number}})
    in
        GroupedRows

    Thank you very much for all your help.  You have been helping me a lot on this forum

    Thanks once again.


    Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com

    Saturday, September 28, 2013 5:20 AM