Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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"
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"
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.
@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.
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.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
97 | |
84 | |
67 | |
60 |
User | Count |
---|---|
150 | |
120 | |
99 | |
87 | |
68 |