Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Power query row to column. Pls help

I have this as data. 

 

clientidNameEmail and Role
C0003XYZ[email protected];Informed,Accountable
C0003abc[email protected];Informed,Accountable
C0003Bikas[email protected];Responsible
C0004Ram[email protected];Informed
C0007Patil[email protected];Informed,Accountable
C0007Padma[email protected];Responsible

 

I want the data in the following format pls help -

 

clientidNameEmail and RoleNameEmail and RoleNameEmail and Role
C0003XYZ[email protected];Informed,Accountableabc[email protected];Informed,AccountableBikas[email protected];Responsible
C0004Ram[email protected];Informed    
C0007Patil[email protected];Informed,AccountablePadma[email protected];Responsible  
Need Help

5 Replies

  • wdx223_Daniel's avatar
    wdx223_Daniel
    Icon for Community Champion rankCommunity Champion
    3 years ago

    NewStep= Table.Combine(Table.Group(PreviousStepName,"clientid",{"n",each #table({"cliendid"}&List.TransformMany({1..Table.RowCount(_)},each List.Skip(Table.ColumnNames(_)),(x,y)=>y&" - "&Text.From(x)),{List.Combine(Table.ToList(_,List.Skip))})})[n])

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      Thanks. How to add this to current formula? pls help

       

      Current formula as in advanced editor is -

       

      let
      Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
      #"Added Custom" = Table.AddColumn(Source, "Custom", each [Column1.descp.1]&","&[Column1.descp.2]&","&[Column1.descp.3]),
      #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each Text.Combine(
      List.Select(
      { [Column1.descp.1], [Column1.descp.2], [Column1.descp.3] },
      each _ <> ""
      ),
      ","
      )),
      #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Custom", "Column1.descp.5", "Column1.descp.4", "Column1.descp.3", "Column1.descp.1", "Column1.descp.2", "Column2", "Column1", "Column1.isgroupinfo", "Column1.iscommu", "Column1.rtype", "Column1.id"}),
      #"Added Custom2" = Table.AddColumn(#"Removed Columns", "Custom", each [Column1.info]&";"&[Custom.1]),
      #"Removed Columns1" = Table.RemoveColumns(#"Added Custom2",{"Column1.info", "Custom.1"}),
      #"Renamed Columns" = Table.RenameColumns(#"Removed Columns1",{{"Column1.clientid", "clientid"}, {"Column1.attri", "Name"}, {"Custom", "Email and Role"}})
      in
      #"Renamed Columns"

       

      how should i modify this?

       

      • wdx223_Daniel's avatar
        wdx223_Daniel
        Icon for Community Champion rankCommunity Champion
        3 years ago

        let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        #"Added Custom" = Table.AddColumn(Source, "Custom", each [Column1.descp.1]&","&[Column1.descp.2]&","&[Column1.descp.3]),
        #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each Text.Combine(
        List.Select(
        { [Column1.descp.1], [Column1.descp.2], [Column1.descp.3] },
        each _ <> ""
        ),
        ","
        )),
        #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Custom", "Column1.descp.5", "Column1.descp.4", "Column1.descp.3", "Column1.descp.1", "Column1.descp.2", "Column2", "Column1", "Column1.isgroupinfo", "Column1.iscommu", "Column1.rtype", "Column1.id"}),
        #"Added Custom2" = Table.AddColumn(#"Removed Columns", "Custom", each [Column1.info]&";"&[Custom.1]),
        #"Removed Columns1" = Table.RemoveColumns(#"Added Custom2",{"Column1.info", "Custom.1"}),
        #"Renamed Columns" = Table.RenameColumns(#"Removed Columns1",{{"Column1.clientid", "clientid"}, {"Column1.attri", "Name"}, {"Custom", "Email and Role"}}),

        NewStep= Table.Combine(Table.Group(#"Renamed Columns","clientid",{"n",each #table({"cliendid"}&List.TransformMany({1..Table.RowCount(_)},each List.Skip(Table.ColumnNames(_)),(x,y)=>y&" - "&Text.From(x)),{List.Combine(Table.ToList(_,List.Skip))})})[n])
        in
        NewStep

  • Jakinta's avatar
    Jakinta
    Icon for Solution Sage rankSolution Sage
    3 years ago

    You can try with these steps

     

    Group = Table.Group(PreviousStep, {"clientid"}, {{"A", each let l = List.Union (Table.ToRows( Table.RemoveColumns(_, {"clientid"}))) in Text.Combine(l, "//"),
 type text }}),
    Split = Table.SplitColumn(Group, "A", Splitter.SplitTextByDelimiter("//", QuoteStyle.Csv)),
    Count = {1..Number.RoundUp ((List.Count( Table.ColumnNames(Split)) - 1 )/2,0)},
    Cols = List.Skip (Table.ColumnNames (Source)),
    NewNames = {Table.ColumnNames (Source){0}} & List.Union ( List.Zip ({ List.Transform( Count, each Cols{0} & "_" & Text.From(_) ), List.Transform( Count, each Cols{1} & "_" & Text.From(_) ) })),
    Custom = Table.RenameColumns ( Split, List.Zip ( { Table.ColumnNames(Split) , NewNames }) ),
    ReplacedNulls = Table.ReplaceValue(Custom,null,"",Replacer.ReplaceValue,Table.ColumnNames (Custom))
in
    ReplacedNulls