Forum Discussion

nok's avatar
nok
Icon for Advocate II rankAdvocate II
4 months ago
Solved

Split and rearrange columns

Hi! I have a table in Excel that follows this format:

ID   Project  Cz[01/01/2026]  Cz[01/02/2026]  Cz[01/03/2026]  Fz[01/01/2026]  Fz[01/02/2026]  Fz[01/03/2026]  Fz[01/04/2026]  Fz[01/05/2026]  Fz[01/06/2026]  Fz[01/07/2026]  Fz[01/08/2026]  Az[01/01/2026]  Az[01/02/2026]  
1Project A          300400500991001011021031041051065563
2Project B4557912213124120759375128712322124

 

As you can see, the excel columns follow the format "Text[date]" for every possible date for Cz, Fz and Az values. I want to split the text and the date and generate different columns. The end result would be a table like this:

 

ID    ProjectDateCz       Fz        Az       
1Project A   01/01/2026     3009955
1Project A01/02/202640010063
1Project A01/03/2026500101 
1Project A01/04/2026 102 
1Project A01/05/2026 103 
1Project A01/06/2026 104 
1Project A01/07/2026 105 
1Project A01/08/2026 106 
1Project A01/09/2026   
1Project A01/10/2026   
1Project A01/11/2026   
1Project A01/12/2026   
2Project B01/01/2026 452222
2Project B01/02/2026 5713124
2Project B01/03/2026 91124 
2Project B01/04/2026  120 
2Project B01/05/2026  759 
2Project B01/06/2026  375 
2Project B01/07/2026  1287 
2Project B01/08/2026  123 
2Project B01/09/2026    
2Project B01/10/2026    
2Project B01/11/2026    
2Project B01/12/2026    

 

How can I do this?

Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • MFelix's avatar
    MFelix
    4 months ago

    Hi nok ,

     

    In power query do the following steps:

    • Select the ID and Project Column
    • Transform - Unpivot columns - Unpivot Others

     

    • Select the attribute column
    • Split Column by delimeter

    • Use the [ as delimiter on the split

     

    • On attribute 2 just replace the ] by blank
    • Then rename columns and format has needed

     

    • Select text column
    • Pivot -> Value -> Don't aggregate

     

    Check the full code below:

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("TY27DoAgDEV/hXRmgJaHjPoF7oTJuLiYGP8/tncgMhyaPs7tnSJ52p/7Oo/XrW4+7UoIygRmsDVFRBlDBBkUMIEZLHZkZREavhP/YjbT2ixXk5qI4YGGoWELqdkCpULJS8Unc103x/gA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"ID   " = _t, #"Project  " = _t, #"Cz[01/01/2026]  " = _t, #"Cz[01/02/2026]  " = _t, #"Cz[01/03/2026]  " = _t, #"Fz[01/01/2026]  " = _t, #"Fz[01/02/2026]  " = _t, #"Fz[01/03/2026]  " = _t, #"Fz[01/04/2026]  " = _t, #"Fz[01/05/2026]  " = _t, #"Fz[01/06/2026]  " = _t, #"Fz[01/07/2026]  " = _t, #"Fz[01/08/2026]  " = _t, #"Az[01/01/2026]  " = _t, #"Az[01/02/2026]  " = _t]),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"ID   ", "Project  "}, "Attribute", "Value"),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Other Columns", "Attribute", Splitter.SplitTextByDelimiter("[", QuoteStyle.Csv), {"Attribute.1", "Attribute.2"}),
    #"Replaced Value" = Table.ReplaceValue(#"Split Column by Delimiter","]","",Replacer.ReplaceText,{"Attribute.2"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Replaced Value",{{"Attribute.2", type date}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Attribute.2", "Date"}, {"Attribute.1", "Text"}}),
    #"Pivoted Column" = Table.Pivot(#"Renamed Columns", List.Distinct(#"Renamed Columns"[Text]), "Text", "Value"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Pivoted Column",{{"Cz", Int64.Type}, {"Fz", Int64.Type}, {"Az", Int64.Type}})
in
    #"Changed Type1"

     

2 Replies

  • MFelix's avatar
    MFelix
    Icon for Super User rankSuper User
    4 months ago

    Hi nok ,

     

    In power query do the following steps:

    • Select the ID and Project Column
    • Transform - Unpivot columns - Unpivot Others

     

    • Select the attribute column
    • Split Column by delimeter

    • Use the [ as delimiter on the split

     

    • On attribute 2 just replace the ] by blank
    • Then rename columns and format has needed

     

    • Select text column
    • Pivot -> Value -> Don't aggregate

     

    Check the full code below:

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("TY27DoAgDEV/hXRmgJaHjPoF7oTJuLiYGP8/tncgMhyaPs7tnSJ52p/7Oo/XrW4+7UoIygRmsDVFRBlDBBkUMIEZLHZkZREavhP/YjbT2ixXk5qI4YGGoWELqdkCpULJS8Unc103x/gA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"ID   " = _t, #"Project  " = _t, #"Cz[01/01/2026]  " = _t, #"Cz[01/02/2026]  " = _t, #"Cz[01/03/2026]  " = _t, #"Fz[01/01/2026]  " = _t, #"Fz[01/02/2026]  " = _t, #"Fz[01/03/2026]  " = _t, #"Fz[01/04/2026]  " = _t, #"Fz[01/05/2026]  " = _t, #"Fz[01/06/2026]  " = _t, #"Fz[01/07/2026]  " = _t, #"Fz[01/08/2026]  " = _t, #"Az[01/01/2026]  " = _t, #"Az[01/02/2026]  " = _t]),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"ID   ", "Project  "}, "Attribute", "Value"),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Other Columns", "Attribute", Splitter.SplitTextByDelimiter("[", QuoteStyle.Csv), {"Attribute.1", "Attribute.2"}),
    #"Replaced Value" = Table.ReplaceValue(#"Split Column by Delimiter","]","",Replacer.ReplaceText,{"Attribute.2"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Replaced Value",{{"Attribute.2", type date}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Attribute.2", "Date"}, {"Attribute.1", "Text"}}),
    #"Pivoted Column" = Table.Pivot(#"Renamed Columns", List.Distinct(#"Renamed Columns"[Text]), "Text", "Value"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Pivoted Column",{{"Cz", Int64.Type}, {"Fz", Int64.Type}, {"Az", Int64.Type}})
in
    #"Changed Type1"

     

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    4 months ago

    Hi,

    This M code works

    let
    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"ID   ", "Project  "}, "Attribute", "Value"),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Other Columns", "Attribute", Splitter.SplitTextByDelimiter("[", QuoteStyle.Csv), {"Attribute.1", "Date"}),
    #"Replaced Value" = Table.ReplaceValue(#"Split Column by Delimiter","]","",Replacer.ReplaceText,{"Date"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Replaced Value",{{"ID   ", Int64.Type}, {"Project  ", type text}, {"Attribute.1", type text}, {"Date", type date}, {"Value", Int64.Type}}),
    #"Pivoted Column" = Table.Pivot(#"Changed Type", List.Distinct(#"Changed Type"[Attribute.1]), "Attribute.1", "Value")
in
    #"Pivoted Column"

    Hope this helps.