Forum Discussion

data123's avatar
data123
Frequent Visitor
4 years ago
Solved

Power Query Question

Hi, I have the following data-

 

Sheet 1- (Access for 2020)

 

Name  EmplID  Security role

John    11111    role 1

John    11111    role 2

Ed        2222    role 1

Ed        2222    role 2

Ed        2222    role 3

Ed        2222    role 4

 

Sheet 2- (Access for 2021)

 

Name  EmplID  Security role

John    11111    role 1

John    11111    role 2

John    11111    role 3

Ed        2222    role 1

Ed        2222    role 2

Ed        2222    role 3

 

The result I am trying to get-

 

Name  Security Role in 2020      Security Role 2021

John    role1, role 2                     role1,role2, role3   

Ed       role1, role2, role3,role4    role1,role2,role3

 

 

I have tried a lot of ways to get the resulting visual but I am stuck. Could you give me some ideas on how to get to the resulting visual?

 

Thanks!

Need Help
  • KNP's avatar
    KNP
    4 years ago

    Try this. (PBIX attached)

     

    let
  Source = Excel.Workbook(
    File.Contents("H:\My Drive\Power BI\Community Solutions\Files\SecurityRole.xlsx"),
    null,
    true
  ),
  #"Removed Other Columns" = Table.SelectColumns(Source, {"Name", "Data"}),
  #"Renamed Columns" = Table.RenameColumns(#"Removed Other Columns", {{"Name", "Year"}}),
  #"Added Custom" = Table.AddColumn(#"Renamed Columns", "Custom", each Table.PromoteHeaders([Data])),
  #"Removed Columns" = Table.RemoveColumns(#"Added Custom", {"Data"}),
  #"Expanded Custom" = Table.ExpandTableColumn(
    #"Removed Columns",
    "Custom",
    {"Name ", "EmplID ", "Security role"},
    {"Name ", "EmplID ", "Security role"}
  ),
  #"Grouped Rows" = Table.Group(
    #"Expanded Custom",
    {"Year", "Name ", "EmplID "},
    {
      {"Count", each Table.RowCount(_), Int64.Type},
      {
        "all",
        each _,
        type table [Name = text, #"Name " = text, #"EmplID " = number, Security role = text]
      }
    }
  ),
  Concat = Table.AggregateTableColumn(
    #"Grouped Rows",
    "all",
    {
      {
        "Security role",
        each Text.Combine(List.Transform(_, (x) => Text.From(x)), ", "),
        "Security Role"
      }
    }
  ),
  #"Pivoted Column" = Table.Pivot(Concat, List.Distinct(Concat[Year]), "Year", "Security Role")
in
  #"Pivoted Column"

8 Replies

  • AlexisOlson's avatar
    AlexisOlson
    Super User
    4 years ago

    I'd suggest adding a year column to each table and then appending them together.

     

    Name  EmplID  Security role  Year

    John    11111    role 1             2020

    John    11111    role 2             2020

    John    11111    role 1             2021

    John    11111    role 2             2021

    John    11111    role 3             2021

    etc.

     

    Then you can create a matrix visual with Name on the rows, years on the columns, and a concatenating measure for the values.

    CONCATENATEX ( DISTINCT ( Table1[Security role] ), Table1[Security role], ", " )
    • data123's avatar
      data123
      Frequent Visitor
      4 years ago

      Hi I tried doing that but whenever I add years on the column it displays only one column on the Matrix either 2020 or 2021, the both do not display together. 

    • data123's avatar
      data123
      Frequent Visitor
      4 years ago

      Hi,

       

      I realized the matrix format is very confusing when I write the dax expression.is there is any way to display the result in this format- 

       

      Name    2020     2021

      John      role 1   role 1

      John     role 2    role 2

      John                  role 3

      Ed        role 1   role 1

      Ed       role 2   role 2

      Ed       role 3   role 3

      Ed       role 4    

      • KNP's avatar
        KNP
        Super User
        4 years ago

        You could just do it in Power Query.

        Code:

        let
  Source = Excel.Workbook(
    File.Contents("H:\My Drive\Power BI\Community Solutions\Files\SecurityRole.xlsx"),
    null,
    true
  ),
  #"Removed Other Columns" = Table.SelectColumns(Source, {"Name", "Data"}),
  #"Renamed Columns" = Table.RenameColumns(#"Removed Other Columns", {{"Name", "Year"}}),
  #"Added Custom" = Table.AddColumn(#"Renamed Columns", "Custom", each Table.PromoteHeaders([Data])),
  #"Removed Columns" = Table.RemoveColumns(#"Added Custom", {"Data"}),
  #"Expanded Custom" = Table.ExpandTableColumn(
    #"Removed Columns",
    "Custom",
    {"Name ", "EmplID ", "Security role"},
    {"Name ", "EmplID ", "Security role"}
  ),
  #"Grouped Rows" = Table.Group(
    #"Expanded Custom",
    {"Year", "Name ", "EmplID "},
    {
      {"Count", each Table.RowCount(_), Int64.Type},
      {
        "all",
        each _,
        type table [Name = text, #"Name " = text, #"EmplID " = number, Security role = text]
      }
    }
  ),
  Concat = Table.AggregateTableColumn(
    #"Grouped Rows",
    "all",
    {
      {
        "Security role",
        each Text.Combine(List.Transform(_, (x) => Text.From(x)), ", "),
        "Security Role"
      }
    }
  )
in
  Concat

         You'll need to change the path obviously but I've attached the file and PBIX.