PowerBI Double Counting my data

[ScrubberKing]

When I import data from a folder containing multiple excel documents it is double counting my data. So in other threads this has been said to be a rare bug but it happens to me almost everytime I get data from a folder. Why does this happen? It is doublecounting at the powerquery step and all subsequent power bi visuals will show the inflated data.

I have monkeyed around with the source data quite a bit and found that if I copy the data to a new excel file, delete the original, and refresh then I will get the correct numbers but what is causing it to happen to begin with?

Here is my PowerQuery:

let
Source = Folder.Files("C:\Users\User\Desktop\FolderName"),
#"Removed Other Columns" = Table.SelectColumns(Source,{"Content"}),
#"Added Custom" = Table.AddColumn(#"Removed Other Columns", "GetData", each Excel.Workbook([Content])),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Content"}),
#"Expanded GetData" = Table.ExpandTableColumn(#"Removed Columns", "GetData", {"Name", "Data", "Item", "Kind", "Hidden"}, {"GetData.Name", "GetData.Data", "GetData.Item", "GetData.Kind", "GetData.Hidden"}),
#"Added Custom1" = Table.AddColumn(#"Expanded GetData", "NoHeaders", each Table.PromoteHeaders([GetData.Data])),
#"Removed Other Columns1" = Table.SelectColumns(#"Added Custom1",{"NoHeaders"}),
#"Expanded NoHeaders" = Table.ExpandTableColumn(#"Removed Other Columns1", "NoHeaders", {"Name", "Type", "Name", "Customer Name", "Serial Number", "Part Number", "Invoice Date", "Points Earned", "Last Point Update", "Industry", "Brand"}, {"Name", "Type", "Name", "Customer Name", "Serial Number", "Part Number", "Invoice Date", "Points Earned", "Last Point Update", "Industry", "Brand"}),
#"Changed Type" = Table.TransformColumnTypes(#"Expanded NoHeaders",{{"Points Earned", Int64.Type}, {"Last Point Update", type datetime}, {"Invoice Date", type datetime}})
in
#"Changed Type"

[v-yuezhe-msft · Accepted solution]

@ScrubberKing,

After you connecting to the folder in Power BI Desktop, why not directly expand the content column to get combined data of all excel files?


In your scenario, if you create table in a Excel sheet , Power BI will double count data((both sheet data and table table)) using the above power query code.

Regards,
Lydia

[ScrubberKing]

Seems to work pretty well. My orginal solution was created after watching this video:

https://www.youtube.com/watch?v=a7E29H5ZUmE