Forum Discussion

JB2020's avatar
JB2020
Regular Visitor
6 years ago
Solved

Split one column (mixed titles/data) into two columns

Hi all,

 

I'm new to PowerQuery and PowerBI, and I've been given a task to set up some data for PowerBI (see image below, first column is our data, second and third are what we need to do with it).

 

I'm supposed to take a single column with mixed headers/data and split it into 2 columns where one is just data, and one is just the header repeated for each data point under that header. 

 

Currently in Excel, we have a macro that's doing this that looks something like this:

 

'Move Line Titles one column over and down

Cells.Find(What:="MARKET SCALE", After:=ActiveCell, LookIn:= _
xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
xlNext, MatchCase:=False, SearchFormat:=False).Activate
Selection.Copy
Selection.Offset(1, -1).Select
ActiveSheet.Paste

 

For each title it copies it one to the left and one down, and then fills them down. Then it removes the blanks. Is there any way to do this in PowerBI? The number of entries is always going to be variable so we can't do it by row # but the titles do stay the same every month.

 

All leads are appreciated.

 

Need Help
  • dax's avatar
    dax
    6 years ago

    Hi  JB2020,

    You could try below M code

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8i9QitWJVnL2MTQyMcLKNAEzXYNcEUKm6MzgcEcXN4SgGVamKbqZjs6uQIAQNUdREAsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [column1 = _t]),
    #"Added Custom" = Table.AddColumn(Source, "Custom", each if Text.StartsWith([column1],"CL") then [column1] else null),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each if Text.StartsWith([column1],"CL") then null else [column1]),
    #"Filled Down" = Table.FillDown(#"Added Custom1",{"Custom.1"})
in
    #"Filled Down"

    Best Regards,
    Zoe Zhi

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

2 Replies

  • dax's avatar
    dax
    Community Support
    6 years ago

    Hi  JB2020,

    You could try below M code

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8i9QitWJVnL2MTQyMcLKNAEzXYNcEUKm6MzgcEcXN4SgGVamKbqZjs6uQIAQNUdREAsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [column1 = _t]),
    #"Added Custom" = Table.AddColumn(Source, "Custom", each if Text.StartsWith([column1],"CL") then [column1] else null),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each if Text.StartsWith([column1],"CL") then null else [column1]),
    #"Filled Down" = Table.FillDown(#"Added Custom1",{"Custom.1"})
in
    #"Filled Down"

    Best Regards,
    Zoe Zhi

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

    • JB2020's avatar
      JB2020
      Regular Visitor
      6 years ago

      Thank you, your solution works perfectly for the data we've presented! Unfortunately they don't all start with CL - I've marked it as solved for anyone who needs a similar solution, but this is what my manager came up with which also works:

       

      [FILEPATH] being the actual filepath of course:

      let
    Source = Excel.Workbook(File.Contents("[FILEPATH]"), null, true),
    Financial_Statement_Generator_0_Sheet = Source{[Item="Financial_Statement_Generator_0",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Financial_Statement_Generator_0_Sheet, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"iPower", type text}, {"Column2", type any}, {"Column3", type any}, {"Column4", type any}, {"Column5", type any}, {"Column6", type any}, {"Column7", type any}, {"Column8", type any}, {"Column9", type any}, {"Column10", type any}, {"Column11", type any}}),
    #"Removed Top Rows" = Table.Skip(#"Changed Type",7),
    #"Promoted Headers1" = Table.PromoteHeaders(#"Removed Top Rows", [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers1",{{"Operating Unit                       ", type text}, {"    TY", type any}, {"    PL", type any}, {" var to PL", type any}, {"    LY", type any}, {" var to LY", type any}, {"    TY_1", type any}, {"   PL", type any}, {"  var to PL", type any}, {"   LY", type any}, {"   var to LY", type any}}),
    #"Removed Top Rows1" = Table.Skip(#"Changed Type1",1),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Top Rows1",{{"Operating Unit                       ", "OU"}}),
    #"Added Conditional Column" = Table.AddColumn(#"Renamed Columns", "Custom", each if Text.Contains([OU], "MARKET SCALE") then "MARKET SCALE" else if Text.Contains([OU], "PLANNED VARIANCE") then "PLANNED VARIANCE" else if Text.Contains([OU], "ACTUAL VARIANCE") then "ACTUAL VARIANCE" else if Text.Contains([OU], "DISBURSEMENTS") then "DISBURSEMENTS" else if Text.Contains([OU], "OTHER REVENUE") then "OTHER REVENUE" else if Text.Contains([OU], "AR PROV / WOFFS") then "AR PROV / WOFFS" else if Text.Contains([OU], "   LESS: DISBURSEMENTS") then "   LESS: DISBURSEMENTS" else if Text.Contains([OU], "NET REVENUE") then "NET REVENUE" else if Text.Contains([OU], "  RESOURCE COSTS") then "  RESOURCE COSTS" else if Text.Contains([OU], "ENGAGEMENT MARGIN") then "ENGAGEMENT MARGIN" else if Text.Contains([OU], "  INCOME from RESOURCE") then "  INCOME from RESOURCE" else if Text.Contains([OU], "CHARGEABLE HOURS") then "CHARGEABLE HOURS" else null),
    #"Filled Down" = Table.FillDown(#"Added Conditional Column",{"Custom"}),
    #"Filtered Rows" = Table.SelectRows(#"Filled Down", each ([OU] <> "   LESS: DISBURSEMENTS" and [OU] <> "  INCOME from RESOURCE" and [OU] <> "  RESOURCE COSTS" and [OU] <> "ACTUAL VARIANCE" and [OU] <> "AR PROV / WOFFS" and [OU] <> "CHARGEABLE HOURS" and [OU] <> "DISBURSEMENTS" and [OU] <> "ENGAGEMENT MARGIN" and [OU] <> "MARKET SCALE" and [OU] <> "NET REVENUE" and [OU] <> "OTHER REVENUE" and [OU] <> "PLANNED VARIANCE")),
    #"Renamed Columns1" = Table.RenameColumns(#"Filtered Rows",{{"    TY", "Month  TY"}, {"    PL", "Month PL"}, {" var to PL", "Month Var to PL"}, {"    LY", "Month LY"}, {" var to LY", "Month Var to LY"}, {"    TY_1", "YTD TY"}, {"   PL", "YTD PL"}, {"  var to PL", "YTD Var to PL"}, {"   LY", "YTD LY"}, {"   var to LY", "YTD Var to LY"}, {"Custom", "Line Description"}}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Renamed Columns1",{{"Month  TY", type number}, {"Month PL", type number}, {"Month Var to PL", type number}, {"Month LY", type number}, {"Month Var to LY", type number}, {"YTD TY", type number}, {"YTD PL", type number}, {"YTD Var to PL", type number}, {"YTD LY", type number}, {"YTD Var to LY", type number}})
in
    #"Changed Type2"