![]() Thank you to Cathy Benson for sharing her ugly MYOB report with me. Remember it only took me 1 minute and 30 seconds to complete the steps above and next month you can reuse the query, so it'll take you a few seconds to press the Refresh button to clean and transform the new report. Now you’re ready to Close and Load your data into a new, nicely formatted Excel Table:ĭon't be put off by the number of steps involved. In the Query Settings: Properties type a new name in the Name field: Before we load the data into Excel let’s give the query a better name.Select the Name column > click on the Filter button > Sort Ascending: For bonus points let’s sort by the Name column.Rename columns 0 and 1: double click the column headers and type in new names:.Drag and drop the columns to rearrange their order so Value is the third column.Delete the Index column (select and press the DELETE key).Next filter out the rows containing null from column 0 (Names).All we need to do now is use the Fill Up tool we used earlier to replace the null values in column 1 (Entitlements).Now we have the names and entitlement types split into two separate columns (named 0 and 1): In the Pivot Column dialog box choose Entitlements as the values column and under ‘Advanced Options’ choose ‘Don’t Aggregate’: Select the ‘Inserted Modulo’ column > Transform tab > Pivot. Now we can use the Modulo column to pivot the data and split the Name and Entitlement into two columns. ![]() It should look like the image below, with a zero for the rows containing names and a 1 for the type of entitlement: With the Index column selected > Add Column tab > Standard > Modulo:Īt the Modulo dialog box enter 2, which is the Index number for the second record (name) in the Entitlements column.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |