cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
p_fehrenbach
Frequent Visitor

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

Hello,

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

 

Screenshot 2021-02-27 232459.png

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

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.

 

1 ACCEPTED SOLUTION
ImkeF
Super User II
Super User II

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

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

View solution in original post

3 REPLIES 3
ImkeF
Super User II
Super User II

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".

 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

ImkeF
Super User II
Super User II

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

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

View solution in original post

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?

 

Screenshot 2021-02-28 102706.pngScreenshot 2021-02-28 102637.png

 

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"

 

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors