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 want to remove duplicates in Power BI Data tab. I have created a new column and I want to remove duplicates. How I can do that.
We can do it using tranform table but I created a new column in data tab so that column is not appearing in transform tab.
Also, Can i create concatenate in transform table? I want to add text and num value. Its giving an error we cannot combine text and value in tarnsform concatenate.
Thnaks
C
Solved! Go to Solution.
Hi,
This M code should let you comobine text with number
=Text_column&Number.ToText(Numeric_column)
Replace Text_column and Numeric_column with your actua table and column names.
Hi,
You should first remove duplicates in the Query Editor and then create your new column.
I know but I want to create a new column using my post code and state. Then I want to remove the duplicates. In transform tab I cannot combine state and postcode together because one is text value and other one is number value.
How can I combine those two columns in tranform tab?
Hi,
This M code should let you comobine text with number
=Text_column&Number.ToText(Numeric_column)
Replace Text_column and Numeric_column with your actua table and column names.
Thank you so much I found it.
You are welcome.
Hi,
Is there any way that I can get the average of sales according to that state and postcode column.
I want to get the average if because there are duplicate values in state and postcode column. So I want to get the average of all the NSW2019 COLUMN.
How can I do that.
I used below in Data tab and it worked. How can I do it in transform tab?
Hi,
It is best to write a measure (DAX formula) to solve this question rather than write a M language code.
Yea that;'s the issue. I have get the average but I cannot remove duplicates in data tab.
I can remove duplicates in tranform tabl but I cannot get the average it before removing duplicates.
I sthere any way that I can do this?
Hi,
I cnanot understand your question. Share your data, explain the business context and show the expected result.
This is the table,
I want to get the average of lat for 7000 TAS. There are 6 lat records for 7000 TAS. So I want to get the average for that. Likewise I want to get the averageif for all the data.
suburb | postcode | state | Suburb and Postcode | lat | lon | PostCode AND State |
GLEBE | 7000 | TAS | GLEBE7000 | -42.874031 | 147.326354 | 7000 TAS |
HOBART | 7000 | TAS | HOBART7000 | -42.882743 | 147.330234 | 7000 TAS |
MOUNT STUART | 7000 | TAS | MOUNT STUART7000 | -42.873119 | 147.302297 | 7000 TAS |
NORTH HOBART | 7000 | TAS | NORTH HOBART7000 | -42.872319 | 147.314579 | 7000 TAS |
QUEENS DOMAIN | 7000 | TAS | QUEENS DOMAIN7000 | -42.865684 | 147.32872 | 7000 TAS |
WEST HOBART | 7000 | TAS | WEST HOBART7000 | -42.883713 | 147.314907 | 7000 TAS |
HOBART | 7001 | TAS | HOBART7001 | -42.837499 | 147.506162 | 7001 TAS |
TASMAN ISLAND | 7001 | TAS | TASMAN ISLAND7001 | -43.238773 | 148.002958 | 7001 TAS |
NORTH HOBART | 7002 | TAS | NORTH HOBART7002 | -42.899691 | 147.446349 | 7002 TAS |
BATTERY POINT | 7004 | TAS | BATTERY POINT7004 | -42.892767 | 147.333242 | 7004 TAS |
SOUTH HOBART | 7004 | TAS | SOUTH HOBART7004 | -42.892586 | 147.316109 | 7004 TAS |
DYNNYRNE | 7005 | TAS | DYNNYRNE7005 | -42.901018 | 147.314131 | 7005 TAS |
LOWER SANDY BAY | 7005 | TAS | LOWER SANDY BAY7005 | -42.919553 | 147.354439 | 7005 TAS |
Hi,
Click on the Table visual and drag the 2 fields Suburb and Post code to the visual. Write this measure
=average(Data[Lat])
Hope this helps.
I want to get the avarage of lat and vlookup it to ther other sheet. Its not about showing the avaerage in a table.
So without doing thses in Power BI its better to do ot in Excel right? But if there is anyway to remove duplicates in Data tab I can get the correct results.
Hi,
You will need to share that other table as well. Show the exact result that you are expecting to see in the other table.
Thanks. I found a way.
can you please provide it with example.
using the column names. One column is "post code" other one is "state".
@cham , refer, if these can help
https://radacad.com/remove-duplicate-doesnt-work-in-power-query-for-power-bi-here-is-the-solution
https://www.youtube.com/watch?v=QfFCPLOEyRU
https://www.youtube.com/watch?v=rqDdnNxSgHQ
I know how to remove duplicates in transform tab.
I want to remove duplicates in data tab. How can I do that?
Or is there any way that we can add two columns in transform tab whic is one column is text other column is value.
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 |
---|---|
114 | |
99 | |
75 | |
73 | |
49 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |