Summarize table data into new table including missing data

[david-young]

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.

[MarcelBeug · Accepted solution]

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]

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]

@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]

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.

[MarcelBeug]

The code I supplied was Power Query, not DAX.

It can be implemented in the Query Editor by creating a new blank query, go to the Advanced Editor, and replace the default query code by my code.