cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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' 

 

data_in_sashboard.PNG

 

 

 

 fields pane.PNG

 

2 REPLIES 2
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

 

Regards,

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: | |
Highlighted
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.

 

let
    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"}})
in
    #"Renamed Columns4"

 

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 103 members 1,490 guests
Please welcome our newest community members: