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:   Co...
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"
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.

      • Kompo85's avatar
        Kompo85
        Frequent Visitor
        8 years ago

        Thanks Marcel

         

        Apologies for not being clear, I have the same problem as David-Young and am trying to achieve the same thing, however, my understanding of DAX isnt great enough to understand your explanation and apply it.

         

        I figured that as my issue is identical it would make sense to ask for an elaboration on this forum post.