Forum Discussion
Add index column when not blank
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"Pls download solution from https://1drv.ms/u/s!Akd5y6ruJhvhuWHJHjfhdfTOgcI1?e=KNOoA8
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 thnx for the help and support.
This solution seems to work, but I keep having trouble with the date. I get a dataformat error ...
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.
Pls download solution from https://1drv.ms/u/s!Akd5y6ruJhvhuWHJHjfhdfTOgcI1?e=KNOoA8
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"It refers to 2022-03-16.csv file. Hence, I would need few rows of this csv file.
Vijay_A_Verma I've added it to the folder
here's the link https://drive.google.com/file/d/1ApLk8WufuwYcRspmdJOdsrh1qT_U5WcN/view?usp=sharing
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?
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 ...