Power Query Transform columns related to processes and dates of those process

[RicardoContrera]

Hi!

I would like to transform many columns related to "processes" and the dates of those processes in Power Query (Power BI)

The final result would be columns for tax_type, process, process date, and value.



Please let me know how to resolve this. Thanks a million!

Ricardo Contreras

[RossEdwards · Accepted solution]

You'd have to replace my "Source" row with your excel source. You might have some transformations needed prior to my code, but this should do what you want:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8gxzVNJRMjUwMABShvqG+kYGRiZAphFMxAgmYggTMYaIxMYCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [TAX_TYPE = _t, PROCESS_1 = _t, DATE_P1 = _t, PROCESS_2 = _t, DATE_P2 = _t, PROCESS_3 = _t, DATE_P3 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"TAX_TYPE", type text}, {"PROCESS_1", Int64.Type}, {"DATE_P1", type date}, {"PROCESS_2", Int64.Type}, {"DATE_P2", type date}, {"PROCESS_3", Int64.Type}, {"DATE_P3", type date}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"TAX_TYPE"}, "Attribute", "Value"),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Columns", "Attribute", Splitter.SplitTextByEachDelimiter({"_"}, QuoteStyle.Csv, true), {"Attribute", "ID"}),
    #"Remove P from ID" = Table.ReplaceValue(#"Split Column by Delimiter","P","",Replacer.ReplaceText,{"ID"}),
    #"Replaced PROCESS" = Table.ReplaceValue(#"Remove P from ID","PROCESS","VALUE",Replacer.ReplaceText,{"Attribute"}),
    #"Replaced DATE" = Table.ReplaceValue(#"Replaced PROCESS","DATE","DATE_PROCESS",Replacer.ReplaceText,{"Attribute"}),
    #"Added PROCESS" = Table.AddColumn(#"Replaced DATE", "PROCESS", each "PROCESS_" & [ID], type text),
    #"Removed ID" = Table.RemoveColumns(#"Added PROCESS",{"ID"}),
    #"Pivoted Column" = Table.Pivot(#"Removed ID", List.Distinct(#"Removed ID"[Attribute]), "Attribute", "Value", List.Max),
    #"Set Types" = Table.TransformColumnTypes(#"Pivoted Column",{{"DATE_PROCESS", type date}, {"VALUE", Int64.Type}})
in
    #"Set Types"

[dufoq3 · Accepted solution]

Hi @RicardoContrera, different approach here.

Result

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8gxzVNJRMjUwMABShvqG+kYGRiZAphFMxAgmYggTMYaIxMYCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [TAX_TYPE = _t, PROCESS_1 = _t, DATE_P1 = _t, PROCESS_2 = _t, DATE_P2 = _t, PROCESS_3 = _t, DATE_P3 = _t]),
    Transformed = Table.FromRows(List.TransformMany(Table.ToRows(Source),
        each List.Split(List.Skip(_),2),
        (x,y)=> {x{0}} & y ), type table[TAX_TYPE=text, VALUE=number, DATE_PROCESS=date]),
    Ad_Process = Table.AddIndexColumn(Transformed, "PROCESS", 1, 1, Int64.Type),
    Transformed2 = Table.TransformColumns(Ad_Process, {{"VALUE", Number.From}, {"DATE_PROCESS", Date.From}, {"PROCESS", each "PROCESS_" & Text.From(_), type text}})
in
    Transformed2

[RicardoContrera]

For Spanish speakers, I did a video to show one response.

[slorin · Accepted solution]

Hi @RicardoContrera 

Another solution

let
Source = Your_Source,
Date_Column = List.Select(Table.ColumnNames(Source), each Text.StartsWith(_, "DATE")),
Process_Column = List.Select(Table.ColumnNames(Source), each Text.StartsWith(_, "PROCESS")),
Data = Table.AddColumn(Source, "Data", 
     each Table.FromColumns(
            {Process_Column,
             Record.ToList(Record.SelectFields(_, Date_Column)),
             Record.ToList(Record.SelectFields(_, Process_Column))},
            {"PROCESS", "DATE_PROCESS", "VALUE"})),
SelectColumns = Table.SelectColumns(Data,{"TAX_TYPE", "Data"}),
Expand = Table.ExpandTableColumn(SelectColumns, "Data", {"PROCESS", "DATE_PROCESS", "VALUE"}, {"PROCESS", "DATE_PROCESS", "VALUE"})
in
Expand

Stéphane

[RicardoContrera]

Hi Stephane!...  It worked.... I really appreciate your help.

[dufoq3 · Accepted solution]

Hi @RicardoContrera, different approach here.

Result

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8gxzVNJRMjUwMABShvqG+kYGRiZAphFMxAgmYggTMYaIxMYCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [TAX_TYPE = _t, PROCESS_1 = _t, DATE_P1 = _t, PROCESS_2 = _t, DATE_P2 = _t, PROCESS_3 = _t, DATE_P3 = _t]),
    Transformed = Table.FromRows(List.TransformMany(Table.ToRows(Source),
        each List.Split(List.Skip(_),2),
        (x,y)=> {x{0}} & y ), type table[TAX_TYPE=text, VALUE=number, DATE_PROCESS=date]),
    Ad_Process = Table.AddIndexColumn(Transformed, "PROCESS", 1, 1, Int64.Type),
    Transformed2 = Table.TransformColumns(Ad_Process, {{"VALUE", Number.From}, {"DATE_PROCESS", Date.From}, {"PROCESS", each "PROCESS_" & Text.From(_), type text}})
in
    Transformed2

[RicardoContrera]

Hi @dufoq3  I really appreciate your help!!

[RossEdwards · Accepted solution]

You'd have to replace my "Source" row with your excel source. You might have some transformations needed prior to my code, but this should do what you want:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8gxzVNJRMjUwMABShvqG+kYGRiZAphFMxAgmYggTMYaIxMYCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [TAX_TYPE = _t, PROCESS_1 = _t, DATE_P1 = _t, PROCESS_2 = _t, DATE_P2 = _t, PROCESS_3 = _t, DATE_P3 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"TAX_TYPE", type text}, {"PROCESS_1", Int64.Type}, {"DATE_P1", type date}, {"PROCESS_2", Int64.Type}, {"DATE_P2", type date}, {"PROCESS_3", Int64.Type}, {"DATE_P3", type date}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"TAX_TYPE"}, "Attribute", "Value"),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Columns", "Attribute", Splitter.SplitTextByEachDelimiter({"_"}, QuoteStyle.Csv, true), {"Attribute", "ID"}),
    #"Remove P from ID" = Table.ReplaceValue(#"Split Column by Delimiter","P","",Replacer.ReplaceText,{"ID"}),
    #"Replaced PROCESS" = Table.ReplaceValue(#"Remove P from ID","PROCESS","VALUE",Replacer.ReplaceText,{"Attribute"}),
    #"Replaced DATE" = Table.ReplaceValue(#"Replaced PROCESS","DATE","DATE_PROCESS",Replacer.ReplaceText,{"Attribute"}),
    #"Added PROCESS" = Table.AddColumn(#"Replaced DATE", "PROCESS", each "PROCESS_" & [ID], type text),
    #"Removed ID" = Table.RemoveColumns(#"Added PROCESS",{"ID"}),
    #"Pivoted Column" = Table.Pivot(#"Removed ID", List.Distinct(#"Removed ID"[Attribute]), "Attribute", "Value", List.Max),
    #"Set Types" = Table.TransformColumnTypes(#"Pivoted Column",{{"DATE_PROCESS", type date}, {"VALUE", Int64.Type}})
in
    #"Set Types"

[RicardoContrera]

Hi @RossEdwards  for your answer. It really worked!