Forum Discussion

david-young's avatar
david-young
Regular Visitor
8 years ago
Solved

Summarize table data into new table including missing data

Hi,

 

I'm trying to create a summary table but I'd like it to include distinct results from column A and distinct results from column B even if they are not distinct as a pair.  For example:

 

Column A        Column B

  1                      A

  1                      B

  1                      A

  2                      B

  3                      A

 

I'd like this to summarize as:

Column A        Column B

  1                      A

  1                      B

  2                      A

  2                      B

  3                      A

  3                      B

  4                      A

 

Hope this makes sense.

Need Help
  • MarcelBeug's avatar
    MarcelBeug
    8 years ago

    You can first select distinct values in Column A, remove Column B and create a new Column B with the distinct B values from step "Changed Type", expand and type the new column:

     

    let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column A", Int64.Type}, {"Column B", type text}}),
    #"Removed Duplicates" = Table.Distinct(#"Changed Type", {"Column A"}),
    #"Removed Columns" = Table.RemoveColumns(#"Removed Duplicates",{"Column B"}),
    #"Added Custom" = Table.AddColumn(#"Removed Columns", "Column B", each List.Distinct(#"Changed Type"[Column B])),
    #"Expanded Column B" = Table.ExpandListColumn(#"Added Custom", "Column B"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Column B",{{"Column B", type text}})
in
    #"Changed Type1"

5 Replies

  • MarcelBeug's avatar
    MarcelBeug
    Community Champion
    8 years ago

    You can first select distinct values in Column A, remove Column B and create a new Column B with the distinct B values from step "Changed Type", expand and type the new column:

     

    let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column A", Int64.Type}, {"Column B", type text}}),
    #"Removed Duplicates" = Table.Distinct(#"Changed Type", {"Column A"}),
    #"Removed Columns" = Table.RemoveColumns(#"Removed Duplicates",{"Column B"}),
    #"Added Custom" = Table.AddColumn(#"Removed Columns", "Column B", each List.Distinct(#"Changed Type"[Column B])),
    #"Expanded Column B" = Table.ExpandListColumn(#"Added Custom", "Column B"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Column B",{{"Column B", type text}})
in
    #"Changed Type1"
    • Kompo85's avatar
      Kompo85
      Frequent Visitor
      8 years ago

      I am having a similar problem but unfortunately the above doesnt make sense to me. 

       

      Trying to get BI to show data that isnt there is something that I have had problems with for some time now. I understand why it can't show it (it doesnt know about what isnt there) but I have charts that need to show consitent data, even if that data has a  count value of 0. 

      • MarcelBeug's avatar
        MarcelBeug
        Community Champion
        8 years ago

        Kompo85 Welcome to the forum.

        My suggestion would be for you to create your own topic in which you clearly explain your issue.

        I don't understand why you react to a topic that doesn't make sense to you.

        It isn't helpful to anybody.