Power Query Question

[data123]

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!

[AlexisOlson]

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]

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    

[Syndicate_Admin]

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.

[data123]

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    

[data123]

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]

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.

[data123]

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    

[data123]

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 · Accepted solution]

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"

[data123]

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.