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...
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"
data123's avatar
data123
Frequent Visitor
4 years ago

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

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"