Code for all three examples can be found in sample file that can be downloaded below.
Uncomplete Pivot
After cleaning some data from XML file, I got all the values presented vertically (1). My goal was to transform this table (1) into normal horizontal table (2). This can be done with Table.Pivot function.
Problem is that, above, in the left table we only have 2 columns. That is enough to write down our Table.Pivot function, but our result will not be correct. We will get an error.
Table.Pivot( FillDownID, { "ID", "OrderDate", "Item", "Units" }, "Name", "Value" )
The problem stems from the fact that there are no other columns to make up the rows of our pivot tables. So, we have to generate one more column. That column should uniquely define each row of a new table. We have to use ID values to create our new column.
First, we will use IF expression to create new column (1). After this, we can fill down values in that column, to fill empty spaces (2).
That is all. Now, we can use our Table.Pivot function to create final result, because our source table now has three needed columns.
Concatenated Values
I got an Excel file with values concatenated, as in (1). As always, we want our data presented as a regular table (2).
First we need to untangle concatented values that are tied to the names of contintents. Because we have two columns with such data (Stock and Sale), our first step is to unpivot table (1). Only then can we fix concatenated values.
We will then remove prefix "Continent-" from "Attribute" column (1->3). After that, we will split column "Value" into to two columns (2->4) by using function Table.SplitColumn. Next, we can have our final result by applying Table.Pivot function on the table with corrected columns.
Table.Pivot( SplitValueColumn, { "Sale", "Stock" }, "Attribute", "Value" )Direct Join
We have two tables (1,2). We want to join them into one table (3). First row should be joined with the first row, second row with the second row, etc. Problem is that the first table contains only dimensions, and second table contains only values, there is no column to base our join on. We can not use Table.Join function.
Solution is to flip our columns and rows with Table.Transpose. We'll get transposed tables (1). Each table will have exactly 7 columns because the original tables had 7 rows. Now we can create their union (2) to get table (3). Table (3) has two problems. First problem is that columns and rows are switched, second problem is that we have lost column names. By transposing table (3) we can solve first problem.
Second problem should be solved by taking original column names from tables (1,2). Then, those column names must be applied to the final table (4).
Sample file can be downloaded from here: