Forum Discussion

Bristix22's avatar
Bristix22
Frequent Visitor
2 years ago
Solved

Create new rows based on the column data

Hi, I'm looking to see if I can take one of my tables that has data on each individual and all the teams they are assigned to and transform it so that for every team an individual is apart of, create...
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi Bristix22 

    You can put the following code to advanced editor in power query

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8krMS1XSUSouBRIlqcUlCiWpiblAtoGeGUwksbg4tQRZWqE8Ix+sxEQpVidaKSQfpCE3EUikZRalIkwwgonATChPLEktQsgbK8XGAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, #"Role/Title" = _t, #"ADO Team Name 1" = _t, #"ProJ.1 Allocaiom" = _t, #"PROJECT ASSET" = _t, #"ADO Team Name 2#(lf)" = _t, #"ProJ.2 Allocaiom#(lf)" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"Role/Title", type text}, {"ADO Team Name 1", type text}, {"ProJ.1 Allocaiom", type number}, {"PROJECT ASSET", type text}, {"ADO Team Name 2#(lf)", type text}, {"ProJ.2 Allocaiom#(lf)", type number}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Name", "Role/Title","PROJECT ASSET"}, "Attribute", "Value"),
    Custom1 = Table.TransformColumns(#"Unpivoted Other Columns",{"Attribute",each Text.Remove(_,{"0".."9","."})}),
    #"Cleaned Text" = Table.TransformColumns(Custom1,{{"Attribute", Text.Clean, type text}}),
    #"Grouped Rows" = Table.Group(#"Cleaned Text", {"Attribute"}, {{"Count", each Table.AddIndexColumn(_,"Index",1,1),type table}}),
    #"Expanded Count" = Table.ExpandTableColumn(#"Grouped Rows", "Count", {"Name", "Role/Title", "PROJECT ASSET", "Value", "Index"}, {"Name", "Role/Title", "PROJECT ASSET", "Value", "Index"}),
    #"Pivoted Column" = Table.Pivot(#"Expanded Count", List.Distinct(#"Expanded Count"[Attribute]), "Attribute", "Value"),
    #"Removed Columns" = Table.RemoveColumns(#"Pivoted Column",{"Index"})
in
    #"Removed Columns"

    Output

    Best Regards!

    Yolo Zhu

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