Table transformation - Separate columns

[tonyclifton]

Hello community,

I'm failing in trying to turn this table into below desired output:

Input (please copy, in case you want to test yourself):

ColumnNames	1	2
2017	null	null
xyz	11	12
DEAB	null	null
abc	13	14
2017	null	null
xyz	22	23
ESYZ	null	null
abc	33	34
2018	null	null
xyz	44	45
DEAB	null	null
abc	55	56
2018	null	null
xyz	66	67
ESYZ	null	null
abc	77	78

Output:

Explanation: 

• Column "1" and "2" of Input represent the month in the output table (for simplicity I didn't enter all 12 month columns).
• I should then be able to use fill function on those 12 newly created rows 
• I need to find a way to move the product and year values to separate columns
  • it would be sufficent to look for text.length = 4
  • for example I tried with: try Number.FromText([ColumnNames]) otherwise null
    • this returns only year and product values but I couldn't find a way to get both into an own column

I hope you can help me.

If something is unclear just let me know.

Thank you.

[v-danhe-msft]

Hi @tonyclifton,

Based on my test, you could refer to below steps:
First copy your row table for two times:

Filter the column equals to 'abc' and 'xyz' then unpivot [1] and [2], sort it:

Copy the filtered table again and filter 'abc', filter the row table 'xyz', merge them as new and add an index column:

In table 2, you could refere below code:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wcs7PKc3N80vMTS1W0lEyBGIjpVidaCUjA0NzICevNCcHRoGEKyqrQMpA6gwhCl1cHZ2wKExMSgapMQYRJoRNNDICEcZgEdfgyCicJhqDTDSGm2iB00QTExBhStiNpqYgwoywiWZmIMKcsBvNQf40t1CKjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"(blank)" = _t, #"(blank).1" = _t, #"(blank).2" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"(blank)", type text}, {"(blank).1", Int64.Type}, {"(blank).2", Int64.Type}}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]),
    #"Filtered Rows" = Table.SelectRows(#"Promoted Headers", each ([ColumnNames] = "2017" or [ColumnNames] = "2018")),
    #"Replaced Value" = Table.ReplaceValue(#"Filtered Rows",null,1000,Replacer.ReplaceValue,{"1"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value",null,2000,Replacer.ReplaceValue,{"2"}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Replaced Value1", {"ColumnNames"}, "Attribute", "Value"),
    #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Columns",{"Value"}),
    #"Added Index" = Table.AddIndexColumn(#"Removed Columns", "Index", 0, 1)
in
    #"Added Index"

In table 3, you could refer to below code:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wcs7PKc3N80vMTS1W0lEyBGIjpVidaCUjA0NzICevNCcHRoGEKyqrQMpA6gwhCl1cHZ2wKExMSgapMQYRJoRNNDICEcZgEdfgyCicJhqDTDSGm2iB00QTExBhStiNpqYgwoywiWZmIMKcsBvNQf40t1CKjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"(blank)" = _t, #"(blank).1" = _t, #"(blank).2" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"(blank)", type text}, {"(blank).1", Int64.Type}, {"(blank).2", Int64.Type}}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]),
    #"Filtered Rows" = Table.SelectRows(#"Promoted Headers", each ([ColumnNames] = "DEAB" or [ColumnNames] = "ESYZ")),
    #"Replaced Value" = Table.ReplaceValue(#"Filtered Rows",null,1000,Replacer.ReplaceValue,{"1"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value",null,2000,Replacer.ReplaceValue,{"2"}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Replaced Value1", {"ColumnNames"}, "Attribute", "Value"),
    #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Columns",{"Value"}),
    #"Added Index" = Table.AddIndexColumn(#"Removed Columns", "Index", 0, 1)
in
    #"Added Index"

Merge them all and delete extra columns,you could see the result:

You could download the pbix file to have a view

Regards,

Daniel He

[tonyclifton]

@v-danhe-msftThanks for the detailed explanation. But it looks like it's to static for my source data with many ColumnNames.

I was able to bring in the source data in the following format:

I

Product	Year	ColumnNames	1	2
DEAB	2017	xyz	11	12
DEAB	2017	abc	13	14
ESYZ	2017	xyz	22	23
ESYZ	2017	abc	33	34
DEAB	2018	xyz	44	45
DEAB	2018	abc	55	56
ESYZ	2018	xyz	66	67
ESYZ	2018	abc	77	78

How can I now get the distinct values in "ColumnNames" transposed?

[tonyclifton · Accepted solution]

I managed to get the source data transformed via SQL.