Power BI - Table missing some rows after split by delimiter operation in the query editor

Question

I am working on the July 2018 Power BI Desktop Version.

I have 100000 records in my table. There is a date column and the earliest date is 1/1/2016.

I am trying to split another column by a delimiter in the query editor and once I perform that some of the records are missing in the query editor table.

But on the front end, I am still able to see the earliest date as 1/1/2016 where as the Query Editor doesn't show up that particular record. I need this in the Query Editor, because when I perform the Group by Min Date, it takes the wrong value.

I haven't faced this problem in the previous versions. I copy pasted the query in the May 2018 version and it is working as expected.

let
    Source = Sql.Database("Server", "DATABASE"),
    TABLE= Source{[Schema="dbo",Item="TABLE"]}[Data],

    #"Split Column by Delimiter" = Table.SplitColumn(TABLE, "COLUMN", Splitter.SplitTextByEachDelimiter({"_"}, QuoteStyle.None, true), {"COLUMN.1", "COLUMN.2"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"COLUMN.1", type text}, {"COLUMN.2", type text}})
in
    #"Changed Type"

I could see all the records before splitting but after splitting by delimter the delimited new column shows only null records and the records could be seen missing as some of the dates are not found.

Does clicking Refresh Preview help at all? Sometimes things get cached oddly. — Alexis Olson
@AlexisOlson:- Do you know the solution for this - stackoverflow.com/questions/51769762/… — Siddharth Thanga Mariappan

Siddharth Thanga Mariappan Siddharth Thanga Mariappan · Accepted Answer · 2018-08-01T16:57:15

Clearing caches under the Data Load solved the Problem.

Power BI - Table missing some rows after split by delimiter operation in the query editor

1 Answers