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

[vishnuragas]

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.

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

 

[v-shex-msft]

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

[vishnuragas]

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"