Index of subgroups

[Anonymous]

Hi!

I need help indexing like the left most column here. So I basically track students in different school-classes, taking different courses.
I need to create an index that stays with the name in the same class. (One waay to think of it is what number that person would be in a list of all the people in the class). I have found a ton of guides telling me how to index subgroups but not a solution to this particular problem. As in my example there may be students with the same name in different classes.

I want to later skip the students' names and just keep their initials. When doing that there is a high likelyhood of duplicates, which is why i want to connect the index to them, so 1.P.C is defferent from 13.P.C in 6A for example. But I can not figure out how to get this indexing. Help would be appreciated!

I am not that adversed in BI so a clean solution in power query would be amazing. I will be getting several files and combining them, there are about 20 classes and roughly 30 students in each class.

So from this index column I can easily deduce that the class list for 6A is:
1. Carlson, Peter
2. Lovely, Sara
3. Xi, Wi Tung
(Ofc. the actual classes contain many more students)

How do I create this index column!?
// Niklas

[tackytechtom]

Hi @Nucko ,

I believe I have a solution for you:

Here the code in Power Query M that you can paste into the advanced editor (if you do not know, how to exactly do this, please check out this quick walkthrough)

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("hdFND4IgGADgv8I8c8gsy2NZWwdrbbZsax2YMmURNNSa/z7EFllBB3HAM96v49HxZw50QiRoyRkEW1xhIQ/WqCpK+R/Lb+ScoMnFKcEsxUrZ5ZLllJTFU7paRvyGaQNBjAT6iDw0KmPcD9eP6pnfuyLWOc/qEs6zOxdnwnK5m6g8X/ZAIEgI2NXq8r0Qz4R+1jG3TWSq4pqdftF1u8ViX1X7f6LrPrrDdhlou2QZFhck8Yak5x/WQvvtDPTIW71HDKw4rSCI6rT5StfvZNimq9SCM0SzXgsC1TCT+x6mbMHpAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Class = _t, Name = _t, Subject = _t, #"Nr of Lessons" = _t, Attended = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Class", type text}, {"Name", type text}, {"Subject", type text}, {"Nr of Lessons", Int64.Type}, {"Attended", Int64.Type}}),
    #"Sorted Rows" = Table.Buffer(Table.Sort(Source,{{"Class", Order.Ascending}, {"Name", Order.Ascending}})),
    #"Grouped Rows" = Table.Group(#"Sorted Rows", {"Class"}, {{"AllData", each InnerGroupAddIndex(_), Value.Type(Table.AddColumn(#"Sorted Rows", "Index", each 1, Int64.Type))}}),
    InnerGroupAddIndex = (Table as table) as table =>
    let
        #"Grouped Rows" = Table.Group(Table, {"Name"}, {{"AllData", each _, Value.Type(Table)}}),
        #"Added Index" = Table.AddIndexColumn(#"Grouped Rows", "Index", 1, 1),
        #"Expanded AllData" = Table.ExpandTableColumn(#"Added Index", "AllData", {"Class"}, {"Class"})
    in
        #"Expanded AllData",

    #"Expanded AllData" = Table.ExpandTableColumn(#"Grouped Rows", "AllData", {"Name", "Index"}, {"Name", "Index"})
in
    #"Expanded AllData"

It is essentially the same solution that was discussed here:

Solved: Custom column Index by two columns group - Microsoft Power BI Community

Does this fix your issue?  

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/

[Anonymous]

Thanks Tom!
I have confirmed that this works in my given example. Please forgive my n00bness, but could you maybe help me integrate this in my current project? I'm very new at this and keep getting errors. I am starting with a very messy data file and have had to clean it up a few ways. (And I know some of them are probably horribly ugly solutions to someone like you)

This is how my data looks like after the clean-up, and it is the leftmost columns Class and Name that is th focus as in my example.

And here is the code so far. Could you maybe guide me where and how to get your suggestion in there. (As I said, I am very new, only discovered Power Query yesterday. So a copy-paste-solution based of this would make my day.)

let
    Source = Folder.Files("F:\Arbete\Frånvaroprojekt\Frånvarofiler\Dagsrapporter-Ämne"),
    #"Filtered Hidden Files1" = Table.SelectRows(Source, each [Attributes]?[Hidden]? <> true),
    #"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File (6)", each #"Transform File (6)"([Content])),
    #"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),
    #"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File (6)"}),
    #"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File (6)", Table.ColumnNames(#"Transform File (6)"(#"Sample File (6)"))),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded Table Column1",{{"Source.Name", type text}, {"Frånvaro per elev och ämne", type text}, {"Column2", type any}, {"Column3", type text}, {"Column4", type any}, {"Column5", type any}, {"Column6", type text}, {"Column7", type any}, {"Column8", type any}, {"Column9", type any}, {"Column10", type text}, {"Column11", type any}, {"Column12", type text}, {"Column13", type any}, {"Column14", type text}, {"Column15", type any}, {"Column16", type text}, {"Column17", type any}, {"Column18", type text}, {"Column19", type any}, {"Column20", Int64.Type}, {"Column21", type text}, {"Column22", type any}, {"Column23", Int64.Type}, {"Column24", type any}, {"Column25", type any}, {"Column26", type any}, {"Column27", type any}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Source.Name", "Column2", "Column4", "Column5", "Column7", "Column8", "Column9", "Column11", "Column13", "Column15", "Column17", "Column18", "Column19", "Column20", "Column21", "Column22", "Column23", "Column24", "Column25", "Column26", "Column27"}),
    #"Added Conditional Column" = Table.AddColumn(#"Removed Columns", "Datum", each if Text.Contains([Frånvaro per elev och ämne], "Till och") then [Frånvaro per elev och ämne] else null),
    #"Added Conditional Column1" = Table.AddColumn(#"Added Conditional Column", "Custom", each if Text.Contains([Frånvaro per elev och ämne], ",") then [Frånvaro per elev och ämne] else null),
    #"Added Conditional Column2" = Table.AddColumn(#"Added Conditional Column1", "Class", each if [Frånvaro per elev och ämne] = "6A" then [Frånvaro per elev och ämne] else if [Frånvaro per elev och ämne] = "6B" then [Frånvaro per elev och ämne] else if [Frånvaro per elev och ämne] = "6C" then [Frånvaro per elev och ämne] else if [Frånvaro per elev och ämne] = "6D" then [Frånvaro per elev och ämne] else if [Frånvaro per elev och ämne] = "6E" then [Frånvaro per elev och ämne] else if [Frånvaro per elev och ämne] = "6F" then [Frånvaro per elev och ämne] else if [Frånvaro per elev och ämne] = "7A" then [Frånvaro per elev och ämne] else if [Frånvaro per elev och ämne] = "7B" then [Frånvaro per elev och ämne] else if [Frånvaro per elev och ämne] = "7C" then [Frånvaro per elev och ämne] else if [Frånvaro per elev och ämne] = "7D" then [Frånvaro per elev och ämne] else if [Frånvaro per elev och ämne] = "7E" then [Frånvaro per elev och ämne] else if [Frånvaro per elev och ämne] = "7F" then [Frånvaro per elev och ämne] else if [Frånvaro per elev och ämne] = "8A" then [Frånvaro per elev och ämne] else if [Frånvaro per elev och ämne] = "8B" then [Frånvaro per elev och ämne] else if [Frånvaro per elev och ämne] = "8C" then [Frånvaro per elev och ämne] else if [Frånvaro per elev och ämne] = "8D" then [Frånvaro per elev och ämne] else if [Frånvaro per elev och ämne] = "8E" then [Frånvaro per elev och ämne] else if [Frånvaro per elev och ämne] = "8F" then [Frånvaro per elev och ämne] else if [Frånvaro per elev och ämne] = "9A" then [Frånvaro per elev och ämne] else if [Frånvaro per elev och ämne] = "9B" then [Frånvaro per elev och ämne] else if [Frånvaro per elev och ämne] = "9C" then [Frånvaro per elev och ämne] else if [Frånvaro per elev och ämne] = "9D" then [Frånvaro per elev och ämne] else if [Frånvaro per elev och ämne] = "9E" then [Frånvaro per elev och ämne] else if [Frånvaro per elev och ämne] = "9F" then [Frånvaro per elev och ämne] else null),
    #"Filtered Rows" = Table.SelectRows(#"Added Conditional Column2", each ([Column14] <> "(tim)")),
    #"Filled Down" = Table.FillDown(#"Filtered Rows",{"Datum", "Custom", "Class"}),
    #"Renamed Columns" = Table.RenameColumns(#"Filled Down",{{"Frånvaro per elev och ämne", "ÄmnesKod"}, {"Column3", "ÄmnesNamn"}, {"Column6", "LektionsTid"}, {"Column10", "FrånvaroTid"}, {"Column12", "AnmäldTid"}, {"Column14", "EjAnmäldTid"}, {"Column16", "RapporteradNärvaro"}}),
    #"Filtered Rows1" = Table.SelectRows(#"Renamed Columns", each ([ÄmnesNamn] <> null and [ÄmnesNamn] <> "Ämnesnamn")),
    #"Reordered Columns" = Table.ReorderColumns(#"Filtered Rows1",{"Class", "Custom", "ÄmnesKod", "ÄmnesNamn", "LektionsTid", "FrånvaroTid", "AnmäldTid", "EjAnmäldTid", "RapporteradNärvaro", "Datum"}),
    #"Replaced Value" = Table.ReplaceValue(#"Reordered Columns",null,"0",Replacer.ReplaceValue,{"LektionsTid", "FrånvaroTid", "AnmäldTid", "EjAnmäldTid", "RapporteradNärvaro"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Replaced Value",{{"LektionsTid", type number}, {"FrånvaroTid", type number}, {"AnmäldTid", type number}, {"EjAnmäldTid", type number}, {"RapporteradNärvaro", type number}}),
    #"Extracted Text After Delimiter" = Table.TransformColumns(#"Changed Type1", {{"Datum", each Text.AfterDelimiter(_, " : "), type text}}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Extracted Text After Delimiter",{{"Datum", type date}}),
    #"Reordered Columns1" = Table.ReorderColumns(#"Changed Type2",{"Class", "Custom", "Datum", "ÄmnesKod", "ÄmnesNamn", "LektionsTid", "FrånvaroTid", "AnmäldTid", "EjAnmäldTid", "RapporteradNärvaro"}),
    #"Renamed Columns2" = Table.RenameColumns(#"Reordered Columns1",{{"Custom", "Name"}})
in
    #"Renamed Columns2"

Thanks for all the help!

/Niklas

[tackytechtom · Accepted solution]

Hi @Anonymous ,

Here the code in Power Query M that you can paste into the advanced editor (if you do not know, how to exactly do this, please check out this quick walkthrough)

let
    Source = Excel.Workbook(File.Contents("C:\Users\Thomas Oefler\Dropbox\programming projects\forum support\book1.xlsx"), null, true),
    Sheet2_Sheet = Source{[Item="Sheet2",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Sheet2_Sheet, [PromoteAllScalars=true]),
    #"Removed Columns" = Table.RemoveColumns(#"Promoted Headers",{"Column2", "Column4", "Column5", "Column7", "Column8", "Column9", "Column11", "Column13", "Column15", "Column17"}),
    #"Added Conditional Column" = Table.AddColumn(#"Removed Columns", "Custom", each if Text.Contains([Frånvaro per elev och ämne], "Till och") then [Frånvaro per elev och ämne] else null),
    #"Added Conditional Column1" = Table.AddColumn(#"Added Conditional Column", "Namn", each if Text.Contains([Frånvaro per elev och ämne], ",") then [Frånvaro per elev och ämne] else null),
    #"Added Conditional Column2" = Table.AddColumn(#"Added Conditional Column1", "Klass", each if [Frånvaro per elev och ämne] = "6A" then "6A" else if [Frånvaro per elev och ämne] = "6B" then "6B" else if [Frånvaro per elev och ämne] = "6C" then "6C" else if [Frånvaro per elev och ämne] = "6D" then "6D" else if [Frånvaro per elev och ämne] = "6E" then "6E" else if [Frånvaro per elev och ämne] = "7A" then "7A" else if [Frånvaro per elev och ämne] = "7B" then "7B" else if [Frånvaro per elev och ämne] = "7C" then "7C" else if [Frånvaro per elev och ämne] = "7D" then "7D" else if [Frånvaro per elev och ämne] = "7E" then "7E" else if [Frånvaro per elev och ämne] = "8A" then "8A" else if [Frånvaro per elev och ämne] = "8B" then "8B" else if [Frånvaro per elev och ämne] = "8C" then "8C" else if [Frånvaro per elev och ämne] = "8D" then "8D" else if [Frånvaro per elev och ämne] = "8E" then "8E" else if [Frånvaro per elev och ämne] = "9A" then "9A" else if [Frånvaro per elev och ämne] = "9B" then "9B" else if [Frånvaro per elev och ämne] = "9C" then "9C" else if [Frånvaro per elev och ämne] = "9D" then "9D" else if [Frånvaro per elev och ämne] = "9E" then "9E" else null),
    #"Filtered Rows" = Table.SelectRows(#"Added Conditional Column2", each ([Column14] <> "(tim)")),
    #"Filled Down" = Table.FillDown(#"Filtered Rows",{"Custom", "Namn", "Klass"}),
    #"Filtered Rows1" = Table.SelectRows(#"Filled Down", each ([Column3] <> null and [Column3] <> "Ämnesnamn")),
    #"Extracted Text After Delimiter" = Table.TransformColumns(#"Filtered Rows1", {{"Custom", each Text.AfterDelimiter(_, ": ", {0, RelativePosition.FromEnd}), type text}}),
    #"Replaced Value" = Table.ReplaceValue(#"Extracted Text After Delimiter",null,0,Replacer.ReplaceValue,{"Column6", "Column10", "Column12", "Column14", "Column16"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Replaced Value",{{"Column6", type number}, {"Column10", type number}, {"Column12", type number}, {"Column14", type number}, {"Column16", type number}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type2",{{"Column3", "Ämne"}, {"Frånvaro per elev och ämne", "ÄmnesKod"}, {"Column6", "LektionsTid"}, {"Column10", "FrånvaroTid"}, {"Column12", "AnmäldTid"}, {"Column14", "EjAnmäldTid"}, {"Column16", "RapporteradNärvaro"}, {"Custom", "Datum"}}),
    #"Reordered Columns1" = Table.ReorderColumns(#"Renamed Columns",{"Klass", "Namn", "Datum", "Ämne", "ÄmnesKod", "LektionsTid", "FrånvaroTid", "AnmäldTid", "EjAnmäldTid", "RapporteradNärvaro"}),
    #"Sorted Rows" = Table.Buffer(Table.Sort(#"Reordered Columns1",{{"Klass", Order.Ascending}, {"Namn", Order.Ascending}})),
    #"Grouped Rows" = Table.Group(#"Sorted Rows", {"Klass"}, {{"AllData", each InnerGroupAddIndex(_), Value.Type(Table.AddColumn(#"Sorted Rows", "Index", each 1, Int64.Type))}}),
    InnerGroupAddIndex = (Table as table) as table =>
    let
        #"Grouped Rows" = Table.Group(Table, {"Namn"}, {{"AllData", each _, Value.Type(Table)}}),
        #"Added Index" = Table.AddIndexColumn(#"Grouped Rows", "Index", 1, 1),
        #"Expanded AllData" = Table.ExpandTableColumn(#"Added Index", "AllData", {"Klass", "Datum", "Ämne", "ÄmnesKod", "LektionsTid", "FrånvaroTid", "AnmäldTid", "EjAnmäldTid", "RapporteradNärvaro"}, {"Klass", "Datum", "Ämne", "ÄmnesKod", "LektionsTid", "FrånvaroTid", "AnmäldTid", "EjAnmäldTid", "RapporteradNärvaro"})
    in
        #"Expanded AllData",

    #"Expanded AllData" = Table.ExpandTableColumn(#"Grouped Rows", "AllData", {"Klass", "Namn", "Datum", "Ämne", "ÄmnesKod", "LektionsTid", "FrånvaroTid", "AnmäldTid", "EjAnmäldTid", "RapporteradNärvaro", "Index"}, {"Klass.1", "Namn", "Datum", "Ämne", "ÄmnesKod", "LektionsTid", "FrånvaroTid", "AnmäldTid", "EjAnmäldTid", "RapporteradNärvaro", "Index"})
in
    #"Expanded AllData"

Have a great week 🙂

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/