feed visuals and tables based on different CSV files

[andreshernandez]

Hi,

I'm looking for some ideas on how to feed my visuals and tables based on different CSV files.

I have some data that I have to show in a stacked column visual per day. The data that I download doesn't include a column with the date of the data and the file name is downloaded with the name of the report and the Date/Time of the download. Each file is around 40 MBytes.

To give you an idea, below is what I have to show everyday

So is expected to see a stacked bar for each day, and each stacked bar would be a file. The name of the files is like:

CPE throughput and QOS 2022-11-11.csv

When I import the file, I transform it to add new columns. In those columns I'm creating one with the date (as the file doesn't come with that info) and some others based on the existing columns. Below are the columns I'm creating:

The formulas for each step are:

• Add_RSRP_Quality: = Table.AddColumn(IMSI_Text_Type, "RSRP Quality", each if (List.Average({[RSRP0 Average],[RSRP1 Average],[RSRP2 Average],[RSRP3 Average]}) >= -80) then "1. RSRP Excellent" else if (List.Average({[RSRP0 Average],[RSRP1 Average],[RSRP2 Average],[RSRP3 Average]}) < -80) and (List.Average({[RSRP0 Average],[RSRP1 Average],[RSRP2 Average],[RSRP3 Average]}) >= -90) then "2. RSRP Good" else if (List.Average({[RSRP0 Average],[RSRP1 Average],[RSRP2 Average],[RSRP3 Average]}) < -90) and (List.Average({[RSRP0 Average],[RSRP1 Average],[RSRP2 Average],[RSRP3 Average]}) >= -100) then "3. RSRP Fair" else "4. RSRP Poor")

• Add_SINR_Quality: = Table.AddColumn(Add_RSRP_Quality, "SINR Quality", each if (List.Average({[SINR0 Average],[SINR1 Average],[SINR2 Average],[SINR3 Average]}) >= 20) then "1. SINR Excellent" else if (List.Average({[SINR0 Average],[SINR1 Average],[SINR2 Average],[SINR3 Average]}) < 20) and (List.Average({[SINR0 Average],[SINR1 Average],[SINR2 Average],[SINR3 Average]}) >= 13) then "2. SINR Good" else if (List.Average({[SINR0 Average],[SINR1 Average],[SINR2 Average],[SINR3 Average]}) < 13) and (List.Average({[SINR0 Average],[SINR1 Average],[SINR2 Average],[SINR3 Average]}) >= 6) then "3. SINR Fair" else "4. SINR Poor")

• Add_QOI_Pack_Att: = Table.AddColumn(Add_SINR_Qualilty, "QOI Pack Att", each if (List.Average({[SINR0 Average],[SINR1 Average],[SINR2 Average],[SINR3 Average]}) >= 18 and List.Average({[RSRP0 Average],[RSRP1 Average],[RSRP2 Average],[RSRP3 Average]}) >= -97) then "50Mbps/10Mbps" else if (List.Average({[SINR0 Average],[SINR1 Average],[SINR2 Average],[SINR3 Average]}) >=16 and List.Average({[RSRP0 Average],[RSRP1 Average],[RSRP2 Average],[RSRP3 Average]}) >= -104) then "25Mbps/5Mbps" else if (List.Average({[SINR0 Average],[SINR1 Average],[SINR2 Average],[SINR3 Average]}) >= 6 and List.Average({[RSRP0 Average],[RSRP1 Average],[RSRP2 Average],[RSRP3 Average]}) >= -106) then "10Mbps/1Mbps" else if (List.Average({[SINR0 Average],[SINR1 Average],[SINR2 Average],[SINR3 Average]}) >=3 and List.Average({[RSRP0 Average],[RSRP1 Average],[RSRP2 Average],[RSRP3 Average]}) >= -108) then "5Mbps/1Mbps" else "Out Of Range")

• Error_Replace_in_SINR_and_RSRP: = Table.ReplaceErrorValues(Add_QOI_Pack_Att, {{"SINR Quality", null},{"RSRP Quality", null},{"QOI Pack Att", null}})

•  Add_Date: = Table.TransformColumnTypes(Table.AddColumn(Error_Replace_in_SINR_and_RSRP, "Date", each if (Date.DayOfWeek(DateTime.LocalNow(), Day.Monday) >= 0 and Date.DayOfWeek(DateTime.LocalNow(), Day.Monday) <=4) then DateTime.Date(Date.AddDays(DateTime.FixedLocalNow(),-1)) else #date(1900,1,1)),{{"Date",type date}})

***Note: The Add Date formula add the day before into the column date, because the info I download is from the day before, and this formula only prints the rigt date if is a weekday; in case of a weekend print a date by default (1/1/1990)

Now you know what I'm doing, I'm looking a way to feed my visuals and table from the CSV files I download and update the visuals and table automatically with the steps mentioned before. Like just dropping the new file in the folder and Power Bi understand that it has to add a new stacked bar in the existing visual with the corresponding steps.

So I need to solve:

1. Does Power Bi can recognize a new source and add it (not replace) to the visuals?

2. Do I need one table per each file or feeding the same table would be the best design?

[andreshernandez]

Hi @KNP ,

The process you gave me worked to upload the files once the Power Bi is refreshed, but is replacing the date on the existing data. I was expecting to have a stackde bar per each day, instead I'm having only one stacked bar but with the double of registers and everything with the same date.

Is there a way to avoid the data overwrite?

I assume is the way I'm adding the date with the step formula in the Transform Sample File:

= Table.AddColumn(#"Replaced Errors", "Add Date", each DateTime.Date(Date.AddDays(DateTime.FixedLocalNow(),-1)))

and the filter used to select the files

= Table.SelectRows(Source, each Text.Contains([Folder Path], "https://******.******.******/sites/Network/TechDev/Public/Performance/Data"))

[KNP]

Yeah, so that will add the same date for every row in every file.

What you need to do is...

Go to your "Removed Other Columns1" step and ensure you keep either the 'Date created' or 'Date modified' (whichever makes sense for your scenario). Power BI removes all columns except the content column by default when using this automated method to combine files. 

Then, when the expand happens, you'll have a date specific to each file against every row. 

I hope that makes sense. Let me know if I need to explain that in more detail.

[KNP]

To answer your questions.

1. Yes it can.
2. All in same table.

Questions.

1. Where are the CSV files stored? (cloud/sharepoint or on-prem)
2. What is your 'Source' step currently?

You'll need to use either SharePoint.Files() or Folder.Files() (with a gateway) and then choose to combine the files.

[andreshernandez]

Hi @KNP ,

Thanks for your prompt reply. Answering your questions:

1. One Drive Location in my laptop. But I can move it to Sharepoint if needed.

2. The Source step is:

= Csv.Document(File.Contents("C:\Users\AndresHernandez\OneDrive\Ran Configuration\Projects\RAN Optimization\Power BI Report\CPE throughput and QOS 2022-11-14 18_09.csv"),[Delimiter=",", Columns=20, Encoding=1252, QuoteStyle=QuoteStyle.None])

Could you give some instructions on how to do it?

[KNP · Accepted solution]

OneDrive is basically SharePoint so that's a good start. Just bear in mind, for a more robust solution, having it on an actual SharePoint site would be better, e.g. not your personal OneDrive, you may run into sharing issues.

The first issue is, you're referring to an individual file.

The next is, you're using the local reference to the OneDrive folder, which won't refresh without a gateway once it is published to the service. 

We can fix both of these at once.

Whether you use OneDrive or SharePoint the step will be the same.

Your Source step will be something like...

SharePoint.Files("https://yourdomain-my.sharepoint.com/personal/your_name_your_domain", [ApiVersion=15])

or if SharePoint...

SharePoint.Files("https://yourdomain.sharepoint.com/sites/your_site_name", [ApiVersion=15])

You should then get a list of files on the site.

Next step, filter to only show the ones you want, this is usually done by folder/file type etc.

Once you've done that and you're happy you're only showing the files you want to combine, click the combine button and watch the magic start to happen.

Any common transforms you need to do, i.e. changes you want done to all files, get done in the 'Transform Sample File' step. This then replicates to all the files automatically.

There are other tips and more concise ways to do it but they are more advanced. I think this is a good starting point.