Showing results for 
Search instead for 
Did you mean: 
vishnuragas Frequent Visitor
Frequent Visitor

Power BI: Total no. of records (S.No) get aggregated on performing union of CSV


I have 40-50 csv files for different cities. I would want to show the number of records across time filtered by city and hashtag.


  1. The 'S.No' got aggregated and is showing the sum of records (I don't understand why did it shows the sum, shouldn't it be individual record numbers when performing Union?) i.e. Even if individual files have S.No from 1-50, the final table should show S.No from 1-2500. but that is not the case here.

Below image shows the data in query editor.

query editor.PNG



This image shows the table with aggregated sums in 'Data table view' 






 fields pane.PNG


Community Support Team
Community Support Team

Re: Power BI: Total no. of records (S.No) get aggregated on performing union of CSV

Hi @vishnuragas,


Is there any advanced operations in your query table?(combine, merge, append, invoke custom function...)

In addition, it is hard to troubleshoot your scenario without any detail information, can you please provide some detailed ?(e.g. query formula, operation steps, snapshots...)

How to Get Your Question Answered Quickly



Xiaoxin Sheng


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

For learning resources/Release notes, please visit: | |
vishnuragas Frequent Visitor
Frequent Visitor

Re: Power BI: Total no. of records (S.No) get aggregated on performing union of CSV

I'm new to Power BI Tool(so might have missed some information like queries and stuff).  But here is the steps I did:


- While importing data using 'Get Data' option, I appended all CSV files from the folder path.

- After appending the files, I wanted to show the number of tweets per hour for each day.

- But the 'S.No' field was aggregated. 

- I also created separate 'date' and 'hour' columns from the 'DateTime' field. But since the 'S.No' or Index is (Sum of values), I could not show the individual number of records for each hour or day.

- Below the query from the advanced editor if it helps.


    Source = Folder.Files("C:\Users\Desktop\data collection\twitter_new_count"),
    #"Filtered Hidden Files1" = Table.SelectRows(Source, each [Attributes]?[Hidden]? <> true),
    #"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File from twitter_new_count", each #"Transform File from twitter_new_count"([Content])),
    #"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),
    #"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File from twitter_new_count"}),
    #"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File from twitter_new_count", Table.ColumnNames(#"Transform File from twitter_new_count"(#"Sample File"))),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded Table Column1",{{"Source.Name", type text}, {"", Int64.Type}, {"ttext", type text}, {"date", type datetime}, {"isretweet", type logical}, {"retweetcount", Int64.Type}, {"favoritecount", Int64.Type}, {"score", Int64.Type}, {"term", type text}, {"city", type text}, {"country", type text}, {"duplicate", type logical}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each true),
    #"Renamed Columns" = Table.RenameColumns(#"Filtered Rows",{{"", "S.No"}}),
    #"Inserted Date" = Table.AddColumn(#"Renamed Columns", "Date.1", each DateTime.Date([date]), type date),
    #"Inserted Time" = Table.AddColumn(#"Inserted Date", "Time", each DateTime.Time([date]), type time),
    #"Renamed Columns2" = Table.RenameColumns(#"Inserted Time",{{"date", "Datentime"}, {"Date.1", "Date"}}),
    #"Added Index" = Table.AddIndexColumn(#"Renamed Columns2", "Index", 0, 1),
    #"Removed Columns" = Table.RemoveColumns(#"Added Index",{"Index"}),
    #"Added Index1" = Table.AddIndexColumn(#"Removed Columns", "Index", 1, 1),
    #"Renamed Columns3" = Table.RenameColumns(#"Added Index1",{{"Index", "Row Number"}}),
    #"Inserted Hour" = Table.AddColumn(#"Renamed Columns3", "Hour", each Time.Hour([Datentime]), Int64.Type),
    #"Filtered Rows1" = Table.SelectRows(#"Inserted Hour", each true),
    #"Renamed Columns4" = Table.RenameColumns(#"Filtered Rows1",{{"Hour", "Hour of Day"}})
    #"Renamed Columns4"


Helpful resources

Community Highlights

Community Highlights

Find out what's new in the Power BI Community!

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 310 members 3,069 guests