possibility that power querry continues to count when the value above is changed

[p_fehrenbach]

Hello,

I have the following problem with power querry. I have a table with the following columns:

In the 1st column Article ID there are multiple duplicate values.
now I want these to look like this later:

8 - 1
8 - 2
8 - 3
8 - 4
9 - 1
10 - 1
10-2
10-3
12 - 1
12-2
14 - 1
14-2

Is there a possibility that power querry continues to count when the value above is changed and starts counting again from 1 when it changes to a new value?

Many thanks in advance for your help.

Regards Pat.

[ImkeF]

Hi @p_fehrenbach ,

when you expand columns in Power Query, PQ will apply its own sort order.
So I believe the numbers are correct, just not sorted correctly. I'd simply sort them in the Excel-table by "(Vorläufige Artikelnummer) Artikel ID".

[ImkeF · Accepted solution]

Hi @p_fehrenbach ,
please paste the code into the advanced editor and follow the steps:

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

You can also watch this screencast to see how this can be done using the UI.
You have to delete the newly automatically created table expression at the end: NestedIndex in PowerBI - YouTube

[p_fehrenbach]

Hi  @ImkeF  ,

many thanks for your help.

this is nearly the solution to my problem.

now I have the index counter as I wanted. but when I load this in the excel sheet the index numbers are all 1 and the first column is still grouped. Maybe you see my mistake here?

My Code:

let
Quelle = Csv.Document(File.Contents("C:\Users\FehrenbachPatrick\OneDrive - GSV Service GmbH & Co. KG\Desktop\Neuer Ordner\Neuer Ordner (2)\EAN.CSV"),[Delimiter=";", Columns=3, Encoding=1252, QuoteStyle=QuoteStyle.None]),
#"Höher gestufte Header" = Table.PromoteHeaders(Quelle, [PromoteAllScalars=true]),
#"Gruppierte Zeilen" = Table.Group(#"Höher gestufte Header", {"(Vorläufige Artikelnummer) Artikel ID"}, {{"Anzahl", each Table.AddIndexColumn(_,"Index",1,1), type table }}),
#"Erweiterte Anzahl" = Table.ExpandTableColumn(#"Gruppierte Zeilen", "Anzahl", {"(Vorläufige Artikelnummer) Artikel ID", "EAN", "EAN Menge", "Index"}, {"Anzahl.(Vorläufige Artikelnummer) Artikel ID", "Anzahl.EAN", "Anzahl.EAN Menge", "Anzahl.Index"}),
#"Entfernte Spalten" = Table.RemoveColumns(#"Erweiterte Anzahl",{"Anzahl.(Vorläufige Artikelnummer) Artikel ID"})
in
#"Entfernte Spalten"