cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Regular Visitor

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Champion
Community Champion

Re: Summarize table data into new table including missing data

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"
Specializing in Power Query Formula Language (M)

View solution in original post

5 REPLIES 5
Highlighted
Community Champion
Community Champion

Re: Summarize table data into new table including missing data

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"
Specializing in Power Query Formula Language (M)

View solution in original post

Highlighted
Frequent Visitor

Re: Summarize table data into new table including missing data

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. 

Highlighted
Community Champion
Community Champion

Re: Summarize table data into new table including missing data

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

Specializing in Power Query Formula Language (M)
Highlighted
Frequent Visitor

Re: Summarize table data into new table including missing data

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.

Highlighted
Community Champion
Community Champion

Re: Summarize table data into new table including missing data

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.

Specializing in Power Query Formula Language (M)

Helpful resources

Announcements
Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Tech Marathon

Maratón de Soluciones de Negocio Microsoft

Una semana de contenido con +100 sesiones educativas, consultorios, +10 workshops Premium, Hackaton, EXPO, Networking Hall y mucho más!

Top Solution Authors
Top Kudoed Authors