Apply Transformations

[Haripriya_9]

Hi All, 

What kind of transformations we need to apply  on Input table to get Output table format : 
Input :
Column1Column2Column3Column4

	Jun-19	Jul-19	Aug-19
CountryA
Category1	11	12	13
Category2	22	23	24
Category3	33	34	35
CountryB
Category1	101	102	103
Category2	122	123	124
Category3	132	133	134

to get desired format output as shown below

Output :

Categorization	Date	Country A	Country B
Category 1	19-Aug	13	103
Category 1	19-Jul	12	102
Category 1	19-Jun	11	101
Category 2	19-Aug	24	124
Category 2	19-Jul	23	123
Category 2	19-Jun	22	122
Category 3	19-Aug	35	134
Category 3	19-Jul	34	133
Category 3	19-Jun	33	132

[edhans · Accepted solution]

Here is the code for one way to do it:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WUtJRMrTU9SrNgzFyIAzH0nSlWJ1oJef80rySokpHoCgUgUUTS1LT84sqDUGKwYQRiDBGkQQJGYEJYxBhgiIJEjIGEyYgwhTZMic8lhlASLB1Bpj2GRpBSGMwiWmloTHEoRA2UD4WAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t]),
    #"Replaced Value" = Table.ReplaceValue(Source,"",null,Replacer.ReplaceValue,{"Column1", "Column2", "Column3", "Column4"}),
    #"Transposed Table" = Table.Transpose(#"Replaced Value"),
    #"Filled Down" = Table.FillDown(#"Transposed Table",{"Column2", "Column6"}),
    #"Transposed Table1" = Table.Transpose(#"Filled Down"),
    #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table1", [PromoteAllScalars=true]),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Promoted Headers", {"Column1"}, "Attribute", "Value"),
    #"Added Custom" = Table.AddColumn(#"Unpivoted Other Columns", "Country", each if (try Value.Is(Number.From(Text.Start([Value], 1)), Int64.Type))[HasError] then [Value] else null),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "New Value", each if [Country] = null then [Value] else null),
    #"Changed Type" = Table.TransformColumnTypes(#"Added Custom1",{{"New Value", Int64.Type}}),
    #"Filled Down1" = Table.FillDown(#"Changed Type",{"Country"}),
    #"Pivoted Column" = Table.Pivot(#"Filled Down1", List.Distinct(#"Filled Down1"[Country]), "Country", "New Value"),
    #"Filled Down2" = Table.FillDown(#"Pivoted Column",{"CountryB"}),
    #"Filtered Rows" = Table.SelectRows(#"Filled Down2", each ([CountryA] <> null)),
    #"Changed Type1" = Table.TransformColumnTypes(#"Filtered Rows",{{"Attribute", type date}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type1",{{"Attribute", "Date"}}),
    #"Removed Columns" = Table.RemoveColumns(#"Renamed Columns",{"Value"})
in
    #"Removed Columns"

This is the end result.

[edhans · Accepted solution]

THere was no PBIX. Sorry - I did that in Excel's Power Query, but I forgot to tell you how to take my M code above and make use if it directly.

How to use M code provided in a blank query:
1) In Power Query, select New Source, then Blank Query
2) On the Home ribbon, select "Advanced Editor" button
3) Remove everything you see, then paste the M code I've given you in that box.
4) Press Done
5) See this article if you need help using this M code in your model.

[Haripriya_9]

Thank you for the quick response.  I tried above code and working as expected.

[edhans]

Excellent @Haripriya_9 - glad I was able to assist.

[Haripriya_9]

Hi @edhans ,

Thank you for the response.. if possible could you please share the pbix file with above transformations. It wil be helpful.

Thanks in advance

[edhans · Accepted solution]

THere was no PBIX. Sorry - I did that in Excel's Power Query, but I forgot to tell you how to take my M code above and make use if it directly.

How to use M code provided in a blank query:
1) In Power Query, select New Source, then Blank Query
2) On the Home ribbon, select "Advanced Editor" button
3) Remove everything you see, then paste the M code I've given you in that box.
4) Press Done
5) See this article if you need help using this M code in your model.

[edhans · Accepted solution]

Oh, one other clarification. For the Fill Down steps to work, the "cells" must be null, not empty. Null will show the word null in the cell, and empty will show a blank white space. I did a Find/Replace to get rid of the empty values. Just leave the "Find" box blank, and type the word null in the Replace With box if your data has empty instead of null. That is what my Replaced Values step is doing - ensuring the Fill Down works as expected later on.

[edhans · Accepted solution]

Here is the code for one way to do it:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WUtJRMrTU9SrNgzFyIAzH0nSlWJ1oJef80rySokpHoCgUgUUTS1LT84sqDUGKwYQRiDBGkQQJGYEJYxBhgiIJEjIGEyYgwhTZMic8lhlASLB1Bpj2GRpBSGMwiWmloTHEoRA2UD4WAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t]),
    #"Replaced Value" = Table.ReplaceValue(Source,"",null,Replacer.ReplaceValue,{"Column1", "Column2", "Column3", "Column4"}),
    #"Transposed Table" = Table.Transpose(#"Replaced Value"),
    #"Filled Down" = Table.FillDown(#"Transposed Table",{"Column2", "Column6"}),
    #"Transposed Table1" = Table.Transpose(#"Filled Down"),
    #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table1", [PromoteAllScalars=true]),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Promoted Headers", {"Column1"}, "Attribute", "Value"),
    #"Added Custom" = Table.AddColumn(#"Unpivoted Other Columns", "Country", each if (try Value.Is(Number.From(Text.Start([Value], 1)), Int64.Type))[HasError] then [Value] else null),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "New Value", each if [Country] = null then [Value] else null),
    #"Changed Type" = Table.TransformColumnTypes(#"Added Custom1",{{"New Value", Int64.Type}}),
    #"Filled Down1" = Table.FillDown(#"Changed Type",{"Country"}),
    #"Pivoted Column" = Table.Pivot(#"Filled Down1", List.Distinct(#"Filled Down1"[Country]), "Country", "New Value"),
    #"Filled Down2" = Table.FillDown(#"Pivoted Column",{"CountryB"}),
    #"Filtered Rows" = Table.SelectRows(#"Filled Down2", each ([CountryA] <> null)),
    #"Changed Type1" = Table.TransformColumnTypes(#"Filtered Rows",{{"Attribute", type date}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type1",{{"Attribute", "Date"}}),
    #"Removed Columns" = Table.RemoveColumns(#"Renamed Columns",{"Value"})
in
    #"Removed Columns"

This is the end result.