Forum Discussion

nok's avatar
nok
Advocate II
3 months ago
Solved

Split and transform columns into rows

Hi!

I have a table in Excel that follows this format:

Co.[01/01/2026]  Co.
[01/02/2026]  		Co.
[01/03/2026]  		Co.
[01/04/2026]  		Co.
[01/05/2026]  		Co.
[01/06/2026]  		Co.
[01/07/2026]  		Co.
[01/08/2026]  		Co.
[01/09/2026]  		Co.
[01/10/2026]  		Co.
[01/11/2026]  		Co.
[01/12/2026]  		Fo.
[01/01/2026]  		Fo.
[01/02/2026]  		Fo.
[01/03/2026]  		Fo.
[01/04/2026]  		Fo.
[01/05/2026]  		Fo.
[01/06/2026]  		Fo.
[01/07/2026]  		Fo.
[01/08/2026]  		Fo.
[01/09/2026]  		Fo.
[01/10/2026]  		Fo.
[01/11/2026]  		Fo.
[01/12/2026]  		Ac.
[01/01/2026]  		Ac.
[01/02/2026]  		Ac.
[01/03/2026]  		Ac.
[01/04/2026]  		Ac.
[01/05/2026]  		Ac.
[01/06/2026]  		Ac.
[01/07/2026]  		Ac.
[01/08/2026]  		Ac.
[01/09/2026]  		Ac.
[01/10/2026]  		Ac.
[01/11/2026]  		Ac.
[01/12/2026]  
102030405060100200300700900100030040050060070080090010001100120013001400100150200250300350400450500550600650
                                    
                                    

 

As you can see, the Excel columns follow the format "Text. [date]" for every possible date. In my Power BI, I want to transform it into this format:

 

DateCo.    Fo.     Ac.    
01/01/2026  10300100
01/02/202620400150
01/03/202630500200
01/04/202640600250
01/05/202650700300
01/06/202660800350
01/07/2026100900400
01/08/20262001000450
01/09/20263001100500
01/10/20267001200550
01/11/20269001300600
01/12/202610001400650

 

How can I do this?

Data Protection
Need Help
Show and Tell
Tips and Tricks
  • pcoley's avatar
    pcoley
    3 months ago

    nok Please try with this PQ script:

    (change the text YOURPATH, YOURBOOKNAME and SHEETNAME of the first two steps accordling to your file path, excelbookname and sheetname) 

    let
  Source = Excel.Workbook(File.Contents("YOURPATH\YOURBOOKNAME.xlsx"), null, true), 
  SheetWithData = Source{[Item = "SHEETNAME", Kind = "Sheet"]}[Data], 
  #"Filtered Rows" = Table.SelectRows(SheetWithData, each [Column1] <> null and [Column1] <> ""), 
  #"Transposed Table" = Table.Transpose(#"Filtered Rows"), 
  #"Trimmed Text" = Table.TransformColumns(#"Transposed Table", {{"Column1", Text.Trim, type text}}), 
  #"Cleaned Text" = Table.TransformColumns(#"Trimmed Text", {{"Column1", Text.Clean, type text}}), 
  #"Replaced Value" = Table.ReplaceValue(
    #"Cleaned Text", 
    "[", 
    "", 
    Replacer.ReplaceText, 
    {"Column2"}
  ), 
  #"Replaced Value1" = Table.ReplaceValue(
    #"Replaced Value", 
    "]", 
    "", 
    Replacer.ReplaceText, 
    {"Column2"}
  ), 
  #"Changed Type" = Table.TransformColumnTypes(
    #"Replaced Value1", 
    {{"Column2", type date}, {"Column3", Int64.Type}}
  ), 
  #"Pivoted Column" = Table.Pivot(
    #"Changed Type", 
    List.Distinct(#"Changed Type"[Column1]), 
    "Column1", 
    "Column3", 
    List.Sum
  )
in

     



     

5 Replies

  • danextian's avatar
    danextian
    Super User
    3 months ago

    Without promoting the first row to headers, you can simply transpose the whole table and then either replace [ and ] with nothing or use Text.BetweenDelimiters to extract the date. Rename and change the column type as appropriate.

     

  • Shai_Karmani's avatar
    Shai_Karmani
    Super User
    3 months ago

    You can do this with Unpivot then Pivot in Power Query. After loading the table and promoting headers if needed:

     

    1. Select all the data columns and use Transform > Unpivot Columns. You will get an Attribute column (with values like Co. [01/01/2026]) and a Value column.

    2. On the Attribute column, use Transform > Split Column > By Delimiter using " [". Rename the parts to Category and Date.

    3. Clean the Date column by replacing "]" with empty, then change its type to Date.

    4. Select the Category column and use Transform > Pivot Column with Value as the values column, and under Advanced options pick Don't Aggregate.

     

    You will end up with one row per Date and three columns Co., Fo., Ac.

     

    If this helped, a thumbs up and accepting the solution would be appreciated.

     

    Best,

    Shai Karmani

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    3 months ago

    Hi,

    Text.Date appear in 2 rows (not a single one) when pasted in MS Excel.  Is that how the raw data is arranged?

  • pcoley's avatar
    pcoley
    Super User
    3 months ago

    nok Please try with this PQ script:

    (change the text YOURPATH, YOURBOOKNAME and SHEETNAME of the first two steps accordling to your file path, excelbookname and sheetname) 

    let
  Source = Excel.Workbook(File.Contents("YOURPATH\YOURBOOKNAME.xlsx"), null, true), 
  SheetWithData = Source{[Item = "SHEETNAME", Kind = "Sheet"]}[Data], 
  #"Filtered Rows" = Table.SelectRows(SheetWithData, each [Column1] <> null and [Column1] <> ""), 
  #"Transposed Table" = Table.Transpose(#"Filtered Rows"), 
  #"Trimmed Text" = Table.TransformColumns(#"Transposed Table", {{"Column1", Text.Trim, type text}}), 
  #"Cleaned Text" = Table.TransformColumns(#"Trimmed Text", {{"Column1", Text.Clean, type text}}), 
  #"Replaced Value" = Table.ReplaceValue(
    #"Cleaned Text", 
    "[", 
    "", 
    Replacer.ReplaceText, 
    {"Column2"}
  ), 
  #"Replaced Value1" = Table.ReplaceValue(
    #"Replaced Value", 
    "]", 
    "", 
    Replacer.ReplaceText, 
    {"Column2"}
  ), 
  #"Changed Type" = Table.TransformColumnTypes(
    #"Replaced Value1", 
    {{"Column2", type date}, {"Column3", Int64.Type}}
  ), 
  #"Pivoted Column" = Table.Pivot(
    #"Changed Type", 
    List.Distinct(#"Changed Type"[Column1]), 
    "Column1", 
    "Column3", 
    List.Sum
  )
in