Breaking Apart a Large Table (Wide) into Long Format Where Unpivot Doesn't' Work

[shubuya]

I have one table as follows:

Column A, Column B, Column C, Column D, Column E, Column F, Column G   ...  Column n

Each sequence of 1 - 4 columns is a category that could be it's own table. Generally the pattern is 'Dim 1, Dim 2, Fact 1, Fact 2'. I want to append the columns that are of the same type (e.g. C & G are Fact 1 = 'Cost'). They all follow the same pattern, but some sequences contain an extra descriptive dimension or only one Fact. 

If you were viewing these as seperate tables it might look something like this:

*where bold = same column type

Table 1 = A, B, C

Table 2 = D, E, F, G

Table n = X, X, X, X (max of 4 possible columns)

And you could append as such (C & G are the same type in this scenario):

A, B, C, x (x = added custom column with null values)

D, E, G, F

X, X, X, X

So ultimately I want to convert this wide table to one long table with as few operations as possible. The way I am doing it now is creating a reference table for each category (e.g. sequence of 1 - 4 columns, changing the column names so they are consistent, adding/ rearranging columns, and appending them together in one table). This is a lot of steps since I have approx 14 categories.

[parry2k]

@shubuya you have provided all the details but it will be easier if you put sample data in excel sheet and also add a sheet with expected result.

Read this post for best practice to get your answer quickly 

https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490