Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
RicardoContrera
New Member

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

Hi!

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

 

CasePowerQuery.png

 

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

CasePowerQueryResult.png

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

Ricardo Contreras

7 REPLIES 7
RicardoContrera
New Member

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

 

 

 

slorin
Super User
Super User

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

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

dufoq3
Super User
Super User

Hi @RicardoContrera, different approach here.

 

Result

dufoq3_1-1712038782043.png

 

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

 


Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

Hi @dufoq3  I really appreciate your help!!

RossEdwards
Solution Specialist
Solution Specialist

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"

RossEdwards_0-1712035199613.png

 

 

Hi @RossEdwards  for your answer. It really worked!

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors