Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
ScrubberKing
Helper I
Helper I

PowerBI Double Counting my data

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"

1 ACCEPTED SOLUTION
v-yuezhe-msft
Employee
Employee

@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?
1.PNG

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

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

2 REPLIES 2
v-yuezhe-msft
Employee
Employee

@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?
1.PNG

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

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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

 

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.