Forum Discussion

jochendecraene's avatar
4 years ago
Solved

Add index column when not blank

Hi

 

Is it possible to add an index based on fields that or not blank?

In the example I whant to create a unique index for those rows that have a datevalue

 

Thx!

  • See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test (later on when you use the query on your dataset, you will have to change the source appropriately)

     

     

    let
        Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtI3NNI3MjAyUorViVbKK83JATOM9Q0tEMKoPLgiU30zLKIm+qbIOo2MsPJiAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t]),
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}}),
        #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),
        #"Filtered Rows" = Table.SelectRows(#"Added Index", each ([Date] <> null)),
        #"Added Index1" = Table.AddIndexColumn(#"Filtered Rows", "IndexColumn", 1, 1, Int64.Type),
        #"Merged Queries" = Table.NestedJoin(#"Added Index", {"Index"}, #"Added Index1", {"Index"}, "Added Index1", JoinKind.LeftOuter),
        #"Removed Columns" = Table.RemoveColumns(#"Merged Queries",{"Index"}),
        #"Expanded Added Index1" = Table.ExpandTableColumn(#"Removed Columns", "Added Index1", {"IndexColumn"}, {"IndexColumn"})
    in
        #"Expanded Added Index1"

     

     

     

     

  • Vijay_A_Verma's avatar
    Vijay_A_Verma
    4 years ago

    Use this. To presever original sort order, I have added one more Index which is removed at the end.

     

    let
        Bron = Csv.Document(File.Contents("C:\Users\jochendecraene\Desktop\3CX test\2022-03-16.csv"),[Delimiter=",", Columns=12, Encoding=65001, QuoteStyle=QuoteStyle.None]),
        #"Type gewijzigd" = Table.TransformColumnTypes(Bron,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}, {"Column8", type text}, {"Column9", type text}, {"Column10", type text}, {"Column11", type text}, {"Column12", type text}}),
        #"Kolommen verwijderd" = Table.RemoveColumns(#"Type gewijzigd",{"Column2", "Column3", "Column4", "Column10", "Column11", "Column12"}),
        Custom1 = Table.RemoveFirstN(#"Kolommen verwijderd", each [Column1]<>"Call Time"),
        #"Promoted Headers" = Table.PromoteHeaders(Custom1, [PromoteAllScalars=true]),
        #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Call Time", type text}, {"Status", type text}, {"Ringing", type time}, {"Talking", type time}, {"Totals", type text}, {"Cost", type text}}),
        #"Added Custom" = Table.AddColumn(#"Changed Type", "Date", each try Date.From(Text.Start([Call Time],10)) otherwise null),
        #"Added Index2" = Table.AddIndexColumn(#"Added Custom", "OriginalIndex", 0, 1, Int64.Type),
        #"Added Index" = Table.AddIndexColumn(#"Added Index2", "Index", 0, 1, Int64.Type),
        #"Filtered Rows" = Table.SelectRows(#"Added Index", each ([Date] <> null)),
        #"Added Index1" = Table.AddIndexColumn(#"Filtered Rows", "IndexColumn", 1, 1, Int64.Type),
        #"Merged Queries" = Table.NestedJoin(#"Added Index", {"Index"}, #"Added Index1", {"Index"}, "Added Index1", JoinKind.LeftOuter),
        #"Removed Columns" = Table.RemoveColumns(#"Merged Queries",{"Index"}),
        #"Expanded Added Index1" = Table.ExpandTableColumn(#"Removed Columns", "Added Index1", {"IndexColumn"}, {"IndexColumn"}),
        #"Sorted Rows" = Table.Sort(#"Expanded Added Index1",{{"OriginalIndex", Order.Ascending}}),
        #"Removed Columns1" = Table.RemoveColumns(#"Sorted Rows",{"OriginalIndex"})
    in
        #"Removed Columns1"

     

18 Replies

  • Vijay_A_Verma's avatar
    Vijay_A_Verma
    Most Valuable Professional

    See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test (later on when you use the query on your dataset, you will have to change the source appropriately)

     

     

    let
        Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtI3NNI3MjAyUorViVbKK83JATOM9Q0tEMKoPLgiU30zLKIm+qbIOo2MsPJiAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t]),
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}}),
        #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),
        #"Filtered Rows" = Table.SelectRows(#"Added Index", each ([Date] <> null)),
        #"Added Index1" = Table.AddIndexColumn(#"Filtered Rows", "IndexColumn", 1, 1, Int64.Type),
        #"Merged Queries" = Table.NestedJoin(#"Added Index", {"Index"}, #"Added Index1", {"Index"}, "Added Index1", JoinKind.LeftOuter),
        #"Removed Columns" = Table.RemoveColumns(#"Merged Queries",{"Index"}),
        #"Expanded Added Index1" = Table.ExpandTableColumn(#"Removed Columns", "Added Index1", {"IndexColumn"}, {"IndexColumn"})
    in
        #"Expanded Added Index1"

     

     

     

     

    • jochendecraene's avatar
      jochendecraene
      Helper V

      thnx for the quick response!

      I'm a newby to the advanced editor ...

      When I open a blank query and paste the code I get an error 'there is no exel table 5 ...

      • Vijay_A_Verma's avatar
        Vijay_A_Verma
        Most Valuable Professional

        I had updated the post later on. Use below code

        let
            Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtI3NNI3MjAyUorViVbKK83JATOM9Q0tEMKoPLgiU30zLKIm+qbIOo2MsPJiAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t]),
            #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}}),
            #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),
            #"Filtered Rows" = Table.SelectRows(#"Added Index", each ([Date] <> null)),
            #"Added Index1" = Table.AddIndexColumn(#"Filtered Rows", "IndexColumn", 1, 1, Int64.Type),
            #"Merged Queries" = Table.NestedJoin(#"Added Index", {"Index"}, #"Added Index1", {"Index"}, "Added Index1", JoinKind.LeftOuter),
            #"Removed Columns" = Table.RemoveColumns(#"Merged Queries",{"Index"}),
            #"Expanded Added Index1" = Table.ExpandTableColumn(#"Removed Columns", "Added Index1", {"IndexColumn"}, {"IndexColumn"})
        in
            #"Expanded Added Index1"