Forum Discussion

RUPSY777's avatar
RUPSY777
Icon for Helper II rankHelper II
6 years ago
Solved

In power bi - the first column is getting repeated value then count +1

hi All, My power bi dataset has a column where repeated value occurs and each time the same value will occur the count should get increased by 1 ( if Column A=SAME VALUE AS PREIOUS ROWS THEN COUNT= ...
Need Help
  • AnkitBI's avatar
    AnkitBI
    6 years ago

    Hi RUPSY777 

     

    You can do directly in PQ as beow.

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WKkksSVSK1YlWyk1JBtNwAVSZWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Column a" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column a", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Column a"}, {{"AllRows", each Table.AddIndexColumn(_,"Count",1,1), type table [Column a=text,Count = number]}}),
    #"Expanded AllRows" = Table.ExpandTableColumn(#"Grouped Rows", "AllRows", {"Count"}, {"Count"})
in
    #"Expanded AllRows"

     

    Thanks
    Ankit Jain
    Do Mark it as solution if the response resolved your problem. Do Kudo the response if it seems good and helpful.

AnkitBI's avatar
AnkitBI
Icon for Solution Sage rankSolution Sage
6 years ago

Hi RUPSY777 

 

You can do directly in PQ as beow.

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WKkksSVSK1YlWyk1JBtNwAVSZWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Column a" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column a", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Column a"}, {{"AllRows", each Table.AddIndexColumn(_,"Count",1,1), type table [Column a=text,Count = number]}}),
    #"Expanded AllRows" = Table.ExpandTableColumn(#"Grouped Rows", "AllRows", {"Count"}, {"Count"})
in
    #"Expanded AllRows"

 

Thanks
Ankit Jain
Do Mark it as solution if the response resolved your problem. Do Kudo the response if it seems good and helpful.