Excel Magic Trick 1263

DESCRIPTION

Power Query M Code: 10 Cross Tabulated Transformed into 1 Proper Data Set
Tabulated Transformed into 1 Proper Data Set.
These are the steps in using Power Query:
1. Select A1, Ctrl + Shift + End (31 date columns and 1 Species Column)
2. Ctrl + T (uncheck Table has Field Names)
3. Import into Power Query as Table
4. Select whole table and add the Data Type: Text (Transform Ribbon Tab, Any Column Group, Data Type = Text)
5. Duplicate first date column (Right-lick and click Duplicate Column)
6. Select Duplicate Date Column: Transform Ribbon Tab, Data & Time Column Group, Date, Parse. This gives errors for non-dates
7. Select Duplicate Date Column: Transform Ribbon Tab, Any Column Group, Replace Errors = Null
8. Select Duplicate Date Column: Transform Ribbon Tab, Any Column Group, Fill Down
9. Select Duplicate Date Column: Transform Ribbon Tab, Table Group, Group By: 1) Duplicate Date Column, 2) Name = Table, 3) Operation = All Rows
10. Edit code in formula bar: = Table.Group(#”Filled Down”, {“Column2 – Copy”}, {{“Table”, each Table.UnpivotOtherColumns(Table.PromoteHeaders(_) , {“Species”}, “Date”, “Value”) , type table}})
11. Remove First Column
12. Expand All Button, uncheck “Use original column name as prefix”
13. Filter out “Column 33”
14. Parse Dates
15. Data Type for Value = Whole Number
16. Close and Load To

WORKBOOKS




Be the first to comment

Leave a Reply