cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jochendecraene
Helper III
Helper III

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

 

Knipsel.JPG

Thx!

3 ACCEPTED SOLUTIONS
Vijay_A_Verma
Super User
Super User

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"

 

 

 

 

View solution in original post

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"

 

View solution in original post

18 REPLIES 18
jochendecraene
Helper III
Helper III

Thnx very much!

jochendecraene
Helper III
Helper III

Thank for the support!

Vijay_A_Verma
Super User
Super User

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"

 

 

 

 

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

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"

 

This loads properly, but there's another problem now

 

Knipsel.JPG

I think an Excel file should be a good idea here. Download it from - https://1drv.ms/x/s!Akd5y6ruJhvhuVX26tnBWv8NR210?e=QczxR8 

Thx,

I've downloaded the file. The right table is exactly what I need.

But, I'm not shure on what to do with the excel file 

 

In Data menu, click Queries and connections. Now you will be able to see the query, double click on the query and it will open the Power Query. Then you will be able to see the steps. 

@Vijay_A_Verma  thnx for the help and support.

 

This solution seems to work, but I keep having trouble with the date. I get a dataformat error ...

 Knipsel.JPG

Is it possible for you to share some rows from your Excel through Onedrive (preferred) / other file hosting service like Google drive, Box, Dropbox? You can remove all sensitive data from there. 

link to pbi file pbi fle and info 

 

thx!

It refers to 2022-03-16.csv file. Hence, I would need few rows of this csv file.

@Vijay_A_Verma I noticed a problem.

 

When the index column is added, power query changes the order of the rows. This gives me a wrong resultwhen I use the fill down function. Is there any solution to keep the order exact the samen as before the index is added? Knipsel.JPG

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"

 

@Vijay_A_Verma  this works fine

 

I've added an extra index to be able to see the different steps in one ID (see image). Is it possible to restart the numbering each time a new ID is reached? Now the numbering just counts up, wich does the job, but looks weird ...

 

Knipsel.JPG

Helpful resources

Announcements
August 2022 update 768x460.jpg

Check it Out!

Click here to learn more about the August 2022 updates!

August 1 episode 9_no_dates 768x460.jpg

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Top Solution Authors