Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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
Solved! Go to 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"
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
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
For Spanish speakers, I did a video to show one response.
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
Hi Stephane!... It worked.... I really appreciate your help.
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
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"