You can use the GUI to do it this way: Select your LetterColumn and then Transform / GroupBy: Select Add Column / Custom Column: Click opposing arrows at top right of new Custom column to Extract Values from new Custom column: Remove AllData column.
This can be achieved using a named range and a custom function in PowerQuery: Name the cell you need to refer (type in a name into the file left of the formula bar) – e.g. SourceFile Insert a new blank PowerQuery query (PowerQuery ribbon -> From other sources) In the PowerQuery editor, go to View … Read more
To “reverse pivot”, “unpivot” or “flatten”: For Excel 2003: Activate any cell in your summary table and choose Data – PivotTable and PivotChart Report: For later versions access the Wizard with Alt+D, P. For Excel for Mac 2011, it’s ⌘+Alt+P (See here). Select Multiple consolidation ranges and click Next. In “Step 2a of 3”, choose … Read more
M supports two different types of comments: Single line comments can be started with // You can comment out multiple lines or comment out text in the middle of a line using /* */ (e.g. = 1 + /* some comment */ 2) Comments might seem to disappear in the formula bar if they are … Read more
First I would use the List Tools / Transform menu (it should be automatically selected) and click the To Table button. This will give you a single-column table with 2 rows. Then I would click the small Expand button – it will appear in the column headings, just to the right of “Column1”. Uncheck the … Read more
M and DAX are two completely different languages. M is used in Power Query (a.k.a. Get & Transform in Excel 2016) and the query tool for Power BI Desktop. Its functions and syntax are very different from Excel worksheet functions. M is a mashup query language used to query a multitude of data sources. It … Read more
Power Query Power Query is a self-service ETL (Extract, Transform, Load) tool which runs as an Excel add-in. It allows users to pull data from various sources, manipulate said data into a form that suits their needs, and load it into Excel. Power Pivot Power Pivot is an in-memory data modelling component that enables highly-compressed … Read more