Tricky indexing issue when different numbers of rows involved

[masplin]

i am looking at some horribly formatted data that looks like this

Each group of schools is in federation  and that Federation is represented in another sheet by the URN of the first school in each group. So I need somehow to identify the row after the null and use the URN else make them null. I can then fill down

I tried adding an index and using this idea someone had on another post, but it just put 102003 in first row and null for everythnig else. I'm not usre why this doesn't work unless my systax is worng (it worked in a slightly different solution)?

= Table.AddColumn(#"Expanded Added Index1", "Custom", each if [Index]=1 then [URN] else if #"Expanded Added Index1"{[Index]-1}[URN]=null then [URN] else null)

I know Matt allington wrote a blog on using a merging of indexes solution. However in my case sometimes there are 2,3 or 4 schools in a group so this would be very cumbersome. 

Appreciate any help. Basically the new column should be

102003

102003

null

102085

102085

null

etc

[JarroVGIT · Accepted solution]

Well every question is an opportunity to learn. I always knew my M was very weak so I spent the better part of this Sunday trying to read up on it and try to get you an answer. And to be honest, I think I got the hang of it a bit! 😄 This might be very clumpsy or not performant at all (I have no idea what the best practices are when it comes to M) but it does work 🙂 Cool stuff!

This is my M:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("hZA9C8IwEIb/Ssncod8fo1ZEB0Gwi5QOZ420kPYgRH+/l7ulg9ghPPC87+VCuk6dwH7APlWo4iiKPFQfdqpBg/NjAvExIWHfogMTvNAGR/3UFtyEC4UBnZQLdzBahhJCxu5qp2XQjpsUpIR867aCC5dzKzMZoWR1AOvGGd9ulCQnVD/eXBDqf1u4xoXbMCKancyVHvHK78VXHsnKN+Jrj3RrEX1E/wU=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Schoolnames = _t, URN = _t, Index = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Schoolnames", type text}, {"URN", Int64.Type}, {"Index", Int64.Type}}),
    //Function that gets the first index of the federation group.It looks at the current index and searches for empty URNs and returns the latest index + 1 (start of the current Federation). If there are no empty URNs it returns index of 1.
    firstIndexOfFederation = (tablename, index ) => 
        if Table.RowCount(Table.SelectRows(tablename, each ([URN] = null) and ([Index] < index))) = 0 then 1 else Table.Max(Table.SelectRows(tablename, each ([URN] = null) and ([Index] < index)), "Index")[Index]+1,
    //I don't know if this is how it should be done but it returns the corresponding URN of the first index of the Federation group.
    getURN = (tablename, index) => Table.SelectRows(tablename, each [Index] = index)[URN]{0},
    //For each row, calculate firstIndexOfFederation and uses that in getURN.
    #"AddCustom Column" = Table.AddColumn(#"Changed Type", "FederationURN", each getURN(#"Changed Type", firstIndexOfFederation(#"Changed Type", [Index])))

in
    #"AddCustom Column"

I put in some comments in the code for clarity. The result is this (I added a new Federation group to my initial data during testing and debugging):

I answered a lot of questions in this community and I think this one has the weirdest "time spend figuring it out" vs "length of post" ratio 😛 Anyway, does this help you?

By the way, if anybody has any better solution I am very curious! @Jimmy801 I am very curious on your thoughts on this 🙂

EDIT: I found a way to eliminate the getUrn function, I now see how you can get a value from a row by it's index using Table{rowIndex}[Columnname] notation, so the Table.SelectRows() in the getURN function was unnecessary. The new M code is this:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("hZA9C8IwEIb/Ssncod8fo1ZEB0Gwi5QOZ420kPYgRH+/l7ulg9ghPPC87+VCuk6dwH7APlWo4iiKPFQfdqpBg/NjAvExIWHfogMTvNAGR/3UFtyEC4UBnZQLdzBahhJCxu5qp2XQjpsUpIR867aCC5dzKzMZoWR1AOvGGd9ulCQnVD/eXBDqf1u4xoXbMCKancyVHvHK78VXHsnKN+Jrj3RrEX1E/wU=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Schoolnames = _t, URN = _t, Index = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Schoolnames", type text}, {"URN", Int64.Type}, {"Index", Int64.Type}}),
   
    //Function that gets the first index of the federation group.It looks at the current index and searches for empty URNs and returns the latest index + 1 (start of the current Federation). If there are no empty URNs it returns index of 1.
    firstIndexOfFederation = (tablename, index ) => 
        if Table.RowCount(Table.SelectRows(tablename, each ([URN] = null) and ([Index] < index))) = 0 then 1 else Table.Max(Table.SelectRows(tablename, each ([URN] = null) and ([Index] < index)), "Index")[Index]+1,
   
    //For each row, calculate firstIndexOfFederation and use that to reference the correct row to find the URN.
    #"AddCustom Column" = Table.AddColumn(#"Changed Type", "FederationURN", each #"Changed Type"{firstIndexOfFederation(#"Changed Type", [Index])-1}[URN])
in
    #"AddCustom Column"

Kind regards

Djerro123

-------------------------------

If this answered your question, please mark it as the Solution. This also helps others to find what they are looking for.

Keep those thumbs up coming! 🙂

[JarroVGIT]

Hi @masplin 

Please never post pictures of your data. It is better to put in a copyable table, a link to an excel or even PBIX in your question. The reason is: we don't want to spend time typing over data into PBI but rather spend our time helping you 🙂

Anyway, I created sample data to recreate your issue. My solution is based on a DAX calculated column.

Harvard	1000	1
Colombia	1001	2
Total for Federation		3
Yale	1002	4
Princeton	1003	5
Total for Federation		6
MIT	1004	7
Darthmouth	1005	8
Colombia	1006	9

The third column is an Index column, you can add that to your dataset in Power Query

The DAX of the calculated column is:

FedURN = 
VAR _curIndex = 'Table'[Index]
VAR _isFirstFederation = IF(COUNTROWS(FILTER('Table', 'Table'[Index] < _curIndex)) = 0, TRUE(), FALSE())
VAR _indexLastTotalLine = MAXX(FILTER('Table', 'Table'[URN] = BLANK() && 'Table'[Index] < _curIndex), [Index])
RETURN 
IF(_isFirstFederation, LOOKUPVALUE('Table'[URN], 'Table'[Index], 1), 
    LOOKUPVALUE('Table'[URN], 'Table'[Index], _indexLastTotalLine + 1))

The resulting table including the calculated column will be:

Does this meet your requirements?

Kind regards

Djerro123

-------------------------------

If this answered your question, please mark it as the Solution. This also helps others to find what they are looking for.

Keep those thumbs up coming! 🙂

[masplin]

Hi

Sorry about the data.  Yes that's great and works, but its a DAX calcaution. I was hoping to do this in M during the query. Is this possible?  Doing it in DAX just means i have a whole load of blank URN rows to deal with.

Thanks for your help

Mike 

[JarroVGIT · Accepted solution]

Well every question is an opportunity to learn. I always knew my M was very weak so I spent the better part of this Sunday trying to read up on it and try to get you an answer. And to be honest, I think I got the hang of it a bit! 😄 This might be very clumpsy or not performant at all (I have no idea what the best practices are when it comes to M) but it does work 🙂 Cool stuff!

This is my M:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("hZA9C8IwEIb/Ssncod8fo1ZEB0Gwi5QOZ420kPYgRH+/l7ulg9ghPPC87+VCuk6dwH7APlWo4iiKPFQfdqpBg/NjAvExIWHfogMTvNAGR/3UFtyEC4UBnZQLdzBahhJCxu5qp2XQjpsUpIR867aCC5dzKzMZoWR1AOvGGd9ulCQnVD/eXBDqf1u4xoXbMCKancyVHvHK78VXHsnKN+Jrj3RrEX1E/wU=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Schoolnames = _t, URN = _t, Index = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Schoolnames", type text}, {"URN", Int64.Type}, {"Index", Int64.Type}}),
    //Function that gets the first index of the federation group.It looks at the current index and searches for empty URNs and returns the latest index + 1 (start of the current Federation). If there are no empty URNs it returns index of 1.
    firstIndexOfFederation = (tablename, index ) => 
        if Table.RowCount(Table.SelectRows(tablename, each ([URN] = null) and ([Index] < index))) = 0 then 1 else Table.Max(Table.SelectRows(tablename, each ([URN] = null) and ([Index] < index)), "Index")[Index]+1,
    //I don't know if this is how it should be done but it returns the corresponding URN of the first index of the Federation group.
    getURN = (tablename, index) => Table.SelectRows(tablename, each [Index] = index)[URN]{0},
    //For each row, calculate firstIndexOfFederation and uses that in getURN.
    #"AddCustom Column" = Table.AddColumn(#"Changed Type", "FederationURN", each getURN(#"Changed Type", firstIndexOfFederation(#"Changed Type", [Index])))

in
    #"AddCustom Column"

I put in some comments in the code for clarity. The result is this (I added a new Federation group to my initial data during testing and debugging):

I answered a lot of questions in this community and I think this one has the weirdest "time spend figuring it out" vs "length of post" ratio 😛 Anyway, does this help you?

By the way, if anybody has any better solution I am very curious! @Jimmy801 I am very curious on your thoughts on this 🙂

EDIT: I found a way to eliminate the getUrn function, I now see how you can get a value from a row by it's index using Table{rowIndex}[Columnname] notation, so the Table.SelectRows() in the getURN function was unnecessary. The new M code is this:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("hZA9C8IwEIb/Ssncod8fo1ZEB0Gwi5QOZ420kPYgRH+/l7ulg9ghPPC87+VCuk6dwH7APlWo4iiKPFQfdqpBg/NjAvExIWHfogMTvNAGR/3UFtyEC4UBnZQLdzBahhJCxu5qp2XQjpsUpIR867aCC5dzKzMZoWR1AOvGGd9ulCQnVD/eXBDqf1u4xoXbMCKancyVHvHK78VXHsnKN+Jrj3RrEX1E/wU=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Schoolnames = _t, URN = _t, Index = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Schoolnames", type text}, {"URN", Int64.Type}, {"Index", Int64.Type}}),
   
    //Function that gets the first index of the federation group.It looks at the current index and searches for empty URNs and returns the latest index + 1 (start of the current Federation). If there are no empty URNs it returns index of 1.
    firstIndexOfFederation = (tablename, index ) => 
        if Table.RowCount(Table.SelectRows(tablename, each ([URN] = null) and ([Index] < index))) = 0 then 1 else Table.Max(Table.SelectRows(tablename, each ([URN] = null) and ([Index] < index)), "Index")[Index]+1,
   
    //For each row, calculate firstIndexOfFederation and use that to reference the correct row to find the URN.
    #"AddCustom Column" = Table.AddColumn(#"Changed Type", "FederationURN", each #"Changed Type"{firstIndexOfFederation(#"Changed Type", [Index])-1}[URN])
in
    #"AddCustom Column"

Kind regards

Djerro123

-------------------------------

If this answered your question, please mark it as the Solution. This also helps others to find what they are looking for.

Keep those thumbs up coming! 🙂

[Jimmy801 · Accepted solution]

Hello  @JarroVGIT 

thank you for contacting me.

I mean, if the database is small, then everything is fine. Then the solution you have proposed has no issue. The problem with Table.AddColumn and Table.SelectRows is the performance get worse in potency when the database grows. Why?`Because you have to think that for every row the complete database has to be passed and then filtered, meaning the more lines your database has, the more you have to add and filter and the slower it gets every adding and filtering. Using Power Query is always best to try to use group and merge function as much as possible. Here another example that shows a possibility and for sure works a lot quicker on large databases

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("hZA9C8IwEIb/Ssncod8fo1ZEB0Gwi5QOZ420kPYgRH+/l7ulg9ghPPC87+VCuk6dwH7APlWo4iiKPFQfdqpBg/NjAvExIWHfogMTvNAGR/3UFtyEC4UBnZQLdzBahhJCxu5qp2XQjpsUpIR867aCC5dzKzMZoWR1AOvGGd9ulCQnVD/eXBDqf1u4xoXbMCKancyVHvHK78VXHsnKN+Jrj3RrEX1E/wU=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Schoolnames = _t, URN = _t, Index = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Schoolnames", type text}, {"URN", Int64.Type}, {"Index", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "GroupIndex", each if [URN]=null then [Index] else null),
    #"Filled Up" = Table.FillUp(#"Added Custom",{"GroupIndex"}),
    #"Grouped Rows" = Table.Group(#"Filled Up", {"GroupIndex"}, {{"AllRows", each _, type table [Schoolnames=text, URN=number, Index=number, GroupIndex=number]}}),
    Transform = Table.TransformColumns
    (
        #"Grouped Rows",
        {
            {
                "AllRows",
                (tableint)=>let 
                firstrow = tableint[URN]{0},
                Transform = Table.TransformColumns
                (
                    tableint,
                    {
                        {
                            "URN",
                            each firstrow
                        }
                    }
                )
                in 
                Transform
            }
        }
    ),
    #"Expanded AllRows" = Table.ExpandTableColumn(Transform, "AllRows", {"Schoolnames", "URN"}, {"Schoolnames", "URN"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded AllRows",{"GroupIndex"})
in
    #"Removed Columns"

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

[masplin]

Hi Jimmy

Thanks for that.  The table i was working on was fairly small so the orignal solution worked fine. i'll keep your suggestion in mind if I ever need to do similar on a bigger table. i 'm not really familiar with using the Group By functionlaity.

Thanks

mike

[Jimmy801]

Hello @masplin 

thank you for you feedback... is much appreciated 🙂

Have a nice time

Jimmy

[masplin]

wow that's real dedication thanks.  Think I see how it works. Very ingenious!!! 

I've pasted our code onto mine and it's throwing an error as field 'Index' not found which I can't understand. only difference is my table is coming from an excel file?   

let
    Source = Excel.Workbook(Parameter4, null, true),
    Federations_Sheet = Source{[Item="Federations",Kind="Sheet"]}[Data],
    #"Removed Top Rows" = Table.Skip(Federations_Sheet,3),
    #"Promoted Headers" = Table.PromoteHeaders(#"Removed Top Rows", [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"URN", Int64.Type}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Index",{{"Index", Int64.Type}}),
 //Function that gets the first index of the federation group.It looks at the current index and searches for empty URNs and returns the latest index + 1 (start of the current Federation). If there are no empty URNs it returns index of 1.
    firstIndexOfFederation = (tablename, index ) => 
        if Table.RowCount(Table.SelectRows(tablename, each ([URN] = null) and ([Index] < index))) = 0 then 1 else Table.Max(Table.SelectRows(tablename, each ([URN] = null) and ([Index] < index)), "Index")[Index]+1,
    //I don't know if this is how it should be done but it returns the corresponding URN of the first index of the Federation group.
    getURN = (tablename, index) => Table.SelectRows(tablename, each [Index] = index)[URN]{0},
    //For each row, calculate firstIndexOfFederation and uses that in getURN.
    #"AddCustom Column" = Table.AddColumn(#"Changed Type1", "FederationURN", each getURN(#"Changed Type", firstIndexOfFederation(#"Changed Type", [Index])))
in
    #"AddCustom Column"

The data I'm using is on this link https://schools-financial-benchmarking.service.gov.uk/Help/DataSources  the CFR 2018-19 for example.  Sorry after all your hard work I'm being a pest, but think you are very close.

Thanks

Mike

[masplin]

Sorry didn;t spot youre 2nd version but same issue

let
    Source = Folder.Files("U:\Dropbox\Limerston\Schools\Financials"),
    #"Filtered Rows" = Table.SelectRows(Source, each ([Name] <> "england_cfrfull 2014.xlsx")),
    #"Filtered Hidden Files1" = Table.SelectRows(#"Filtered Rows", each [Attributes]?[Hidden]? <> true),
    #"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File (4)", each #"Transform File (4)"([Content])),
    #"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),
    #"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File (4)"}),
    #"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File (4)", Table.ColumnNames(#"Transform File (4)"(#"Sample File (4)"))),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Expanded Table Column1", "Source.Name", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false), {"Source.Name.1", "Source.Name.2"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Source.Name.1", type text}, {"Source.Name.2", type text}}),
    #"Split Column by Delimiter1" = Table.SplitColumn(#"Changed Type", "Source.Name.2", Splitter.SplitTextByEachDelimiter({"."}, QuoteStyle.Csv, false), {"Source.Name.2.1", "Source.Name.2.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"Source.Name.2.1", Int64.Type}, {"Source.Name.2.2", type text}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type1",{{"Source.Name.2.1", "Year"}}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Renamed Columns",{{"URN", type number}, {"FTE", type number}}),
    #"Removed Other Columns" = Table.SelectColumns(#"Changed Type2",{"Year", "School Name", "URN"}),
    #"Added Index" = Table.AddIndexColumn(#"Removed Other Columns", "Index", 1, 1),
    #"Sorted Rows" = Table.Sort(#"Added Index",{{"Index", Order.Ascending}}),
    #"Changed Type3" = Table.TransformColumnTypes(#"Sorted Rows",{{"Index", Int64.Type}}),
 //Function that gets the first index of the federation group.It looks at the current index and searches for empty URNs and returns the latest index + 1 (start of the current Federation). If there are no empty URNs it returns index of 1.
    firstIndexOfFederation = (tablename, index ) => 
        if Table.RowCount(Table.SelectRows(tablename, each ([URN] = null) and ([Index] < index))) = 0 then 1 else Table.Max(Table.SelectRows(tablename, each ([URN] = null) and ([Index] < index)), "Index")[Index]+1,
   
    //For each row, calculate firstIndexOfFederation and use that to reference the correct row to find the URN.
    #"AddCustom Column" = Table.AddColumn(#"Changed Type3", "FederationURN", each #"Changed Type"{firstIndexOfFederation(#"Changed Type", [Index])-1}[URN])
in
    #"AddCustom Column"

[masplin]

sorry that's the folder version. Thisi single file code

let
    Source = Excel.Workbook(Parameter4, null, true),
    Federations_Sheet = Source{[Item="Federations",Kind="Sheet"]}[Data],
    #"Removed Top Rows" = Table.Skip(Federations_Sheet,3),
    #"Promoted Headers" = Table.PromoteHeaders(#"Removed Top Rows", [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"URN", Int64.Type}}),
  //Function that gets the first index of the federation group.It looks at the current index and searches for empty URNs and returns the latest index + 1 (start of the current Federation). If there are no empty URNs it returns index of 1.
    firstIndexOfFederation = (tablename, index ) => 
        if Table.RowCount(Table.SelectRows(tablename, each ([URN] = null) and ([Index] < index))) = 0 then 1 else Table.Max(Table.SelectRows(tablename, each ([URN] = null) and ([Index] < index)), "Index")[Index]+1,
    //I don't know if this is how it should be done but it returns the corresponding URN of the first index of the Federation group.
    getURN = (tablename, index) => Table.SelectRows(tablename, each [Index] = index)[URN]{0},
    //For each row, calculate firstIndexOfFederation and uses that in getURN.
    #"AddCustom Column" = Table.AddColumn(#"Changed Type", "FederationURN", each getURN(#"Changed Type", firstIndexOfFederation(#"Changed Type", [Index])))
in
    #"AddCustom Column"

[masplin]

started working!!!!  brilliant appreciate all your help

[JarroVGIT]

Great to hear! 🙂