What Can I get rid from duplicates values of remaining numeric column after the unpivot table ?

[Roshan_201295]

Hi I have shared my proble in the above picture 

I have finacial data ,there some columns which contains incentive information and other columns contains the numerical values and categorical too.

But ,I have selected incentive columns and created the unpivote table,so that I will get the incentive names at row level and  with their respectives in the value column that is generated while doing unpivot table for these particular incentive columns.

I have done it,But other columns that are not part of incentive columns and containing the numerical values are getting repeat values as many as I have employee id.

So I don't such kind of duplication of the remaining numerical data so please suggest me any tips and tricks !

Thank u ! 

[v-yiruan-msft]

Hi  @Roshan_201295 ,

Whether your problem has been resolved? If yes, could you please mark the helpful post as Answered? It will help the others in the community find the solution easily if they face the same problem as yours. Thank you.

Best Regards

[mahenkj2]

Hi @Roshan_201295,

Suggest you to remove total payout column, before performing unpivot operation. The reason is, such calculation should be avoided if possible, this generally can be done automatically while using pivot table in the Excel file or in visuals in Power Bi.

Another reason is your current data is flat (wide) and you are making it to long, it does not make sense if this total is repeated infront of each ID, since granularity would be with incentive_1 and/or _2.

Once you remove this column, then unpivot as below (select incetive_1 and _2 and unpivot):

This is real picture of your data in long format.

Done. Now use this table as a pivot in excel or in a visual in Power BI.

In excel you can summarize as below:

Hope it helps.

[Roshan_201295]

Hi Reena 

Thanks for showing ur intrest

I have 13 columns of incentive types & I just want to show only those columns which does not contains blanks or zero at all.It means sum of every incentive should not be zero.If in case that incentive contains completely 0 in that case it should hide automatically from table.So please let me if it is possible to get rid from these columns or not ...if possible then thses should should dynamically once I will use slicer for the particular catgorical iterms.

Thank You !

[v-yiruan-msft]

Hi  @Roshan_201295 ,

I created a sample pbix file(see attachment) for you, please check whether that is what you want. You can paste the following codes in your Advanced Editor:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jZFBDsMwCAT/knMOgDGwb4ly7P+/UEOSSlbTqJattSVmWeFtW15MvKwLsyNSKdjr3bgNpfPc7H0tWooyy+pw0SFu6J86ml1oppNCtzTpihJxu284gVoh1VHtvcIODTywdMGZDqSZBU16deUn8gIzGbzAiMrQx/oDPGYKTzVIjtrVZZrS9+2EsxpaHoD48U3l8bvt/gY=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"emp id" = _t, Incentive_1 = _t, Incentive_2 = _t, Incentive_3 = _t, Incentive_4 = _t, Incentive_5 = _t, Incentive_6 = _t, Incentive_7 = _t, Incentive_8 = _t, Incentive_10 = _t, Incentive_11 = _t, Incentive_12 = _t, Incentive_13 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"emp id", type text}, {"Incentive_1", Int64.Type}, {"Incentive_2", Int64.Type}, {"Incentive_3", Int64.Type}, {"Incentive_4", Int64.Type}, {"Incentive_5", Int64.Type}, {"Incentive_6", Int64.Type}, {"Incentive_7", Int64.Type}, {"Incentive_8", Int64.Type}, {"Incentive_10", Int64.Type}, {"Incentive_11", Int64.Type}, {"Incentive_12", Int64.Type}, {"Incentive_13", Int64.Type}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type",0,null,Replacer.ReplaceValue,{"Incentive_1", "Incentive_2", "Incentive_3", "Incentive_4", "Incentive_5", "Incentive_6", "Incentive_7", "Incentive_8", "Incentive_10", "Incentive_11", "Incentive_12", "Incentive_13"}),
    #"Unpivoted Only Selected Columns" = Table.Unpivot(#"Replaced Value", {"Incentive_1", "Incentive_2", "Incentive_3", "Incentive_4", "Incentive_5", "Incentive_6", "Incentive_7", "Incentive_8", "Incentive_10", "Incentive_11", "Incentive_12", "Incentive_13"}, "Incentive", "Values")
in
    #"Unpivoted Only Selected Columns"

If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards

[amitchandak]

@Roshan_201295 , if those are the only value, you can use remove duplicates rows

Remove Empty and Remove duplicate Rows (Power Query) : https://youtu.be/Hc5bIXkpGVE

otherwise in measure you can use summarize

sumx(summarize(Table, Table[employee], Table[Incentive], [Total Payout]), [Total Payout])

[Roshan_201295]

This is not going to help me ...I have multiple numeric values 

[v-yiruan-msft]

Hi @Roshan_201295 ,

How would you like to remove duplicate values from these columns of values other than the categories columns(incentive_1 and incentive_2)? If you just want the final total value to be correct, you could consider adding a custom column (current value divided by the number of categories (for example, there are now two types of incentive incentive_1 and incentive_2, in this case 2)). But then you may need to add a custom column for each of the other values, which is tedious and complicated..
Could you please share the final result you want (visual, data you want to display, etc.), maybe we can achieve it by creating measure without having to do it inside the Power Query Editor...

Measure = CALCULATE(MAX('Table'[Total Payout]))

Best Regards