Data not showing in model

[swinings]

Hello everyone,

I have a problem with PowerBI Desktop. I have transformed my data and removed blanks. However, my data model is only loading some of the rows, not all.

I've tried everything I can think of, even rebuilding in Transform Data, but it is not working.

Shannon

[swinings · Accepted solution]

I have figured out the issue. Someone changed a column heading in the data and it no longer matched what it should.

[Greg_Deckler]

@swinings Can you paste your M code?

[swinings]

Here is my code for the query. However, the items load in Transform Data mode, just not in the data model when I leave the Transform mode.

let
Source = SharePoint.Files("https://meorc365.sharepoint.com/sites/CapacityTracking", [ApiVersion = 15]),
#"Filtered Rows" = Table.SelectRows(Source, each not Text.Contains([Name], "Archive") and not Text.StartsWith([Name], "2023") and not Text.Contains([Name], "2021")),
#"Filtered Hidden Files1" = Table.SelectRows(#"Filtered Rows", each [Attributes]?[Hidden]? <> true),
#"Filtered Hidden Files2" = Table.SelectRows(#"Filtered Hidden Files1", each [Attributes]?[Hidden]? <> true),
#"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files2", "Transform File (2)", each #"Transform File (2)"([Content])),
#"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),
#"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File (2)"}),
#"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File (2)", Table.ColumnNames(#"Transform File (2)"(#"Sample File (2)"))),
#"Changed Type" = Table.TransformColumnTypes(#"Expanded Table Column1",{{"Source.Name", type text}, {"Subject", type text}, {"Start Date", type date}, {"Start Time", type datetime}, {"End Time", type datetime}, {"Categories", type text}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Subject"}),
#"Split Column by Delimiter" = Table.SplitColumn(#"Removed Columns", "Source.Name", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false), {"Source.Name.1", "Source.Name.2"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Source.Name.1", type text}, {"Source.Name.2", type text}}),
#"Removed Columns1" = Table.RemoveColumns(#"Changed Type1",{"Source.Name.2"}),
#"Renamed Columns" = Table.RenameColumns(#"Removed Columns1",{{"Source.Name.1", "Employee"}}),
#"Changed Type2" = Table.TransformColumnTypes(#"Renamed Columns",{{"Start Time", type time}, {"End Time", type time}}),
#"Split Column by Delimiter1" = Table.SplitColumn(#"Changed Type2", "Categories", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), {"Categories.1", "Categories.2"}),
#"Changed Type3" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"Categories.1", type text}, {"Categories.2", type text}}),
#"Renamed Columns2" = Table.RenameColumns(#"Changed Type3",{{"Categories.2", "County"}, {"Categories.1", "Categories"}}),
#"Removed Blank Rows" = Table.SelectRows(#"Renamed Columns2", each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"", null})))
in
#"Removed Blank Rows"

---

[swinings]

@Greg_Deckler I forgot to @ you.