Forum Discussion

KyleFurner's avatar
KyleFurner
Frequent Visitor
5 years ago

Split row into multiple rows, while combining columns

Hi all,

 

I have inherited a dataset which I need to manipulate in Power BI to track project progress. All information is captured monthly in a single table, so all 150+ columns in a single row.

 

For example, here are the columns relating to critical milestones. (headers only)

Milestone 1Milestone 1 StatusMilestone 1 target DateMilestone 2Milestone 2 StatusMilestone 2 target DateMilestone 3Milestone 3 StatusMilestone 3 target Date

 

What I need to do is split these columns out from the main dataset and separate into rows like so (mock data):

MilestoneStatusTarget Date
Milestone 1Milestone 1 StatusMilestone 1 target Date
Milestone 2Milestone 2 StatusMilestone 2 target Date
Milestone 3Milestone 3 StatusMilestone 3 target Date

 

I have to replicate this same process for half a dozen other report widgets, so any help with the code to achieve this would be really appreciated.

 

The following link was useful for the actual splitting and creation of a new table, but I don't know how to maintain the relationship between the name, status and date for individual milestones.

https://community.powerbi.com/t5/Desktop/selected-rows-and-union-of-2-columns-in-new-table/td-p/670158 

Need Help

16 Replies

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    5 years ago

    Hi,

    This M code works

    let
    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    #"Added Index" = Table.AddIndexColumn(Source, "Index", 1, 1, Int64.Type),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Added Index", {"Index"}, "Attribute", "Value"),
    #"Inserted Text After Delimiter" = Table.AddColumn(#"Unpivoted Other Columns", "Text After Delimiter", each Text.AfterDelimiter([Attribute], " ", 1), type text),
    #"Replaced Value" = Table.ReplaceValue(#"Inserted Text After Delimiter","","Milestone",Replacer.ReplaceValue,{"Text After Delimiter"}),
    #"Inserted Text Range" = Table.AddColumn(#"Replaced Value", "Text Range", each Text.Middle([Attribute], 10, 1), type text),
    #"Removed Columns" = Table.RemoveColumns(#"Inserted Text Range",{"Attribute"}),
    #"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[#"Text After Delimiter"]), "Text After Delimiter", "Value"),
    #"Removed Columns1" = Table.RemoveColumns(#"Pivoted Column",{"Index", "Text Range"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Removed Columns1",{{"target Date", type datetime}})
in
    #"Changed Type"

    Hope this helps.

    The Original dataset is this

    • KyleFurner's avatar
      KyleFurner
      Frequent Visitor
      5 years ago

      Hi Ashish,

       

      Thanks for the detailed response. I'm having some difficulty getting this to work. I already have the dataset in Power BI but these specific columns are just a handful from the whole dataset (150+ columns).

       

      Do you have any advice for how I should tweak this?

       

      Apologies if these are ignorant questions.

      • Ashish_Mathur's avatar
        Ashish_Mathur
        Super User
        5 years ago

        You are welcome.  Without your actual data, I really cannot help much.

  • AllisonKennedy's avatar
    AllisonKennedy
    Community Champion
    5 years ago

    KyleFurner  How are the columns named? I would suggest doing an 'unpivot columns' or 'unpivot other columns' in Power Query for this one. If they're named consistently with the milestone name, then you'll get close to what you need after a split/extract on the attribute column. 

     

    If they are named "Milestone Name", "Milestone Status" "Milestone Target" you'll have even more success. 

     

    If the names aren't helpful, you'll need a bit more custom function but still definitely do this in Power Query, NOT DAX. 

     

    Please share sample column names so we can assist further if needed.

    • KyleFurner's avatar
      KyleFurner
      Frequent Visitor
      5 years ago

      Thanks for the quick reply Allison. Sorry if my original post wasn't clear. 

       

      The current column names are:

      Milestone 1

      Milestone 1 Status

      Milestone 1 target Date

      Milestone 2

      Milestone 2 Status

      Milestone 2 target Date

      Milestone 3

      Milestone 3 Status

      Milestone 3 target Date

       

      I am looking to take a single row and split it out so that I have one column for Milestone names, one for status and one for date, but need to make sure the status and date for each milestone is in the same row as the milestone name.

       

      I had planned to have three new columns called "Milestone Name", "Milestone Status" "Milestone Target" to achieve this purpose. 

       

      I also wasn't sure about using the pivot/unpivot functions as these are just a handful of columns in a dataset that has over 150 columns. Would using the pivot/unpivot function affect these other columns?

       

      Kyle