Creating a new Calculated Table using while loops

[Anonymous]

Hello, I am currently trying to make a new table from a data set I have. The data I have is basically this, a length measurement associated with a classification (1 or 2). The data is orginized in a way that the length increments are about 500 ft. and there are about a dozen or so in a row of the same classification before it changes to the other class. What I want to do is create a new table with "segment numbers" for each time the classification changes and get a sum of all those length measurements for that segment.

The left table is what I have, and the right table is what I want. If this is possible, and anyone can help me that would be great

[PaulDBrown · Accepted solution]

Here is one way.

In Power Query, create the segment table by using the equivalent to the following in a blank query:

let
    Source = FTable, // Change "FTable" to point at your fact table
    #"Removed Other Columns" = Table.SelectColumns(Source,{"Class"}),
    #"Removed Duplicates" = Table.Distinct(#"Removed Other Columns"),
    #"Inserted Merged Column" = Table.AddColumn(#"Removed Duplicates", "Segment", each Text.Combine({"Segment ", Text.From([Class], "es-ES")}), type text)
in
    #"Inserted Merged Column"

Once loaded into the model, create a one-to-many relationship between the new Class table and the fact table.

Now you can create a SUm measure for length, and create a table visual using the Segment field and the [Sum length] measure

I've attached the cample PBIX file

[Anonymous]

This is almost what I was hoping for, except the "class" in the original dataset is only 1 or 2. I'm hoping to name segments based on the chronological order the classes come in, ie. the first 6 instances of class 1 is segment 1, then the next 4 rows of class 2 is segment 2, the next rows of class 1 is segment 3 etc.

[PaulDBrown]

Big thumbs up to @Vijay_A_Verma for helping with the code to establish the consecutive segments.

File attached