Reply
Highlighted
Frequent Visitor
Posts: 8
Registered: ‎11-01-2018

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

[ Edited ]

 

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

 

Community Support Team
Posts: 7,479
Registered: ‎08-14-2016

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: | |
Frequent Visitor
Posts: 8
Registered: ‎11-01-2018

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"