POWER BI is duplicating rows after loading in POWER QUERY

[Anonymous]

Hello 😀

I am very new to using Power BI and have self taught myself it using forums and youtube videos.

I need some help, please as I can't seem to find a solution!

In my raw data I have one row

But when i look at it in power query it has duplicated the row. This is happening to only a few random rows and not every single row. 

Any help will be greatly apperciated.

Thank you!

[BA_Pete]

Hi @Anonymous ,

The fact that you have a [Source.Name] column in your output tells me that you've probably missed some information in your post about how you have imported the data into your query.

Can you give details around what method you've used to get your source data into PBI please i.e. have you used the folder connector, by any chance?

Pete

[Anonymous]

Hi @BA_Pete, 

As I have two seperate excel files which I need as 1 file I used the Get Data icon and selected a folder and combined both files (the headers are the same)

Thank you

[BA_Pete]

Ok. For just two files I wouldn't generally recommend using the folder connector as it can get more complicated than it's worth, but we'll see if we can fix it as it is.

Can you select the following two queries, open Advanced Editor and copy the whole M code in each, then paste each into a separate code window ( </> button above ) here please?

Queries to copy/paste:

- 'Transform Sample File From X' - this is so I can see whether your generic transformation is creating duplicates.

- Your main query that is produced by the Combine & Transform process - this is so I can see whether there's a step that's been added after the C&T process has completed that's causing your issue.

Thanks,

Pete

[Anonymous]

this is the code from the main query 

Source = Folder.Files("C:\Oxford Street\Business Insights & Information\Reporting\Appointment Dashboard\2022-23\Other Appts\New folder\Appt Paste"),
#"Filtered Hidden Files1" = Table.SelectRows(Source, each [Attributes]?[Hidden]? <> true),
#"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File", each #"Transform File"([Content])),
#"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),
#"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File"}),
#"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File", Table.ColumnNames(#"Transform File"(#"Sample File"))),
#"Changed Type" = Table.TransformColumnTypes(#"Expanded Table Column1",{{"Source.Name", type text}, {"Date", type date}, {"Store", Int64.Type}, {"Salesperson", Int64.Type}, {"Name", type text}, {"Type", type text}, {"Dept", type text}, {"Transaction", Int64.Type}, {"£ Sales", type number}, {"Unit Sales", Int64.Type}, {"Column10", type any}, {"Column11", type any}, {"Column12", type any}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Column10", "Column11", "Column12"}),
#"Merged Queries" = Table.NestedJoin(#"Removed Columns", {"Date"}, Codes, {"Date"}, "Codes", JoinKind.LeftOuter),
#"Merged Queries1" = Table.NestedJoin(#"Merged Queries", {"Store"}, Store, {"Store Code"}, "Store.1", JoinKind.LeftOuter),
#"Expanded Store.1" = Table.ExpandTableColumn(#"Merged Queries1", "Store.1", {"Store Name", "Storeindex"}, {"Store Name", "Storeindex"}),
#"Expanded Codes" = Table.ExpandTableColumn(#"Expanded Store.1", "Codes", {"Week", "Period", "Year", "Dept Code", "Day", "Date - Copy"}, {"Week", "Period", "Year", "Dept Code", "Day", "Date - Copy"}),
#"Renamed Columns" = Table.RenameColumns(#"Expanded Codes",{{"£ Sales", "Sales"}, {"Unit Sales", "Units"}, {"Transaction", "# of Appts"}, {"Salesperson", "TM ID"}, {"Name", "TM Name"}, {"Date - Copy", "Dayindex"}}),
#"Merged Queries2" = Table.NestedJoin(#"Renamed Columns", {"TM ID"}, TM, {"ID"}, "TM", JoinKind.LeftOuter),
#"Expanded TM" = Table.ExpandTableColumn(#"Merged Queries2", "TM", {"Bucket", "Beauty Brands"}, {"Bucket", "Beauty Brands"}),
#"Filtered Rows" = Table.SelectRows(#"Expanded TM", each ([Week] = 5) and ([TM ID] = 814497))
in
#"Filtered Rows"

and this is the code for the Transform file

let
Source = Excel.Workbook(Parameter1, null, true),
#"Appt Paste_Sheet" = Source{[Item="Appt Paste",Kind="Sheet"]}[Data],
#"Promoted Headers" = Table.PromoteHeaders(#"Appt Paste_Sheet", [PromoteAllScalars=true])
in
#"Promoted Headers"

is this what you needed?

Thank you 

[BA_Pete · Accepted solution]

Hi @Anonymous ,

Yes, that's perfect, thanks.

My guess is that the three merges you are doing in your main query are introducing crossjoins and therefore duplicating rows.

To identify your issue, pick one of the duplicated rows (such as the one in your original example screenshot) and check each source value against the merged table, like this:

1) Take the [Date] value in your duplicated row (04/03/2022 in this example) and filter Codes[Date] by this value. How many rows are there with this [Date] value?

2) Take the [Store] value (9001) and filter Store[Store Code] by this value. How many rows are there?

3) Take the [TM ID] value (814497) and filter TM[ID] by this value. How many rows are there?

If the answer to any/all of the questions above is "more than one", then it is the merges that are creating the duplicate rows.

Pete