Is there anyway in power bi to assign value to distinct count against value

zain qazi 0
Viewed 59

I want to ask that i have a column of claims in which a claim no have different occurances but i want to assign value of 1 when claim no is distinct and it occurred first time then i assign 0 when it is repeated , so is there any method for creating calculated column for this problem

powerquery business-intelligence dax powerbi sql
1 Answers

You can use Power Query to achieve your goal: Below I created some datasets, and achieved a result:

Let's say you have such data set:

First Step

Then here is all steps involved in Power Query to achieve your goal. Just Paste the code into Advanced Editor in which you can find in Home tab and Query Group:

Advanced Editor

If you clicked the Advanced Editor, You can see the full code, like this:

Full Code Page

The Full M-Code:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjMwNDFS0lEyVIrVAfNMzYA8IxgPTc4UWQ6s0hBFpSmKnAmKnBGKKcboZsYCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Claim No" = _t, Occurances = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Claim No", Int64.Type}, {"Occurances", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Claim No"}, {{"Order", each _, type table [Claim No=nullable number, Occurances=nullable number]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([Order],"SNo",1)),
    Custom1 = Table.Combine(#"Added Custom"[Custom]),
    #"Added Custom1" = Table.AddColumn(Custom1, "Distinct_Number", each if [SNo] = 1 then 1 else 0),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"SNo"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Removed Columns",{{"Distinct_Number", Int64.Type}})
in
    #"Changed Type1"

If we test it, It returns:

Resultset

Ozan Sen0
Related