Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
andreshernandez
New Member

feed visuals and tables based on different CSV files

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

 

andreshernandez_0-1668533975144.png

 

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:

 

andreshernandez_1-1668534289389.png

 

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?

 

1 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.

KNP_0-1668543541406.png

 

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.

 

KNP_1-1668543806202.png

 

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

 

Have I solved your problem?
Please click Accept as Solution so I don't keep coming back to this post, oh yeah, others may find it useful also ;).
chrome-9xf-Zagzel-B

If you found this post helpful, please give Kudos.
It gives me a sense of instant gratification and, if you give me Kudos enough times, magical unicorns will appear on your screen.
If you find my signature vaguely amusing, please give Kudos.
KIfp67uy-Sr
Proud to be a Super User!PBI-Super-User-Rank-30x30-1x

View solution in original post

5 REPLIES 5
andreshernandez
New Member

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)))

 

andreshernandez_1-1668694616764.png

 

 

and the filter used to select the files

 

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

 

andreshernandez_0-1668695908051.png

 

 

 

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. 

 

KNP_0-1668707482087.png

 

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

 

Have I solved your problem?
Please click Accept as Solution so I don't keep coming back to this post, oh yeah, others may find it useful also ;).
chrome-9xf-Zagzel-B

If you found this post helpful, please give Kudos.
It gives me a sense of instant gratification and, if you give me Kudos enough times, magical unicorns will appear on your screen.
If you find my signature vaguely amusing, please give Kudos.
KIfp67uy-Sr
Proud to be a Super User!PBI-Super-User-Rank-30x30-1x
KNP
Super User
Super User

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.

 

 

Have I solved your problem?
Please click Accept as Solution so I don't keep coming back to this post, oh yeah, others may find it useful also ;).
chrome-9xf-Zagzel-B

If you found this post helpful, please give Kudos.
It gives me a sense of instant gratification and, if you give me Kudos enough times, magical unicorns will appear on your screen.
If you find my signature vaguely amusing, please give Kudos.
KIfp67uy-Sr
Proud to be a Super User!PBI-Super-User-Rank-30x30-1x

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?

 

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.

KNP_0-1668543541406.png

 

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.

 

KNP_1-1668543806202.png

 

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

 

Have I solved your problem?
Please click Accept as Solution so I don't keep coming back to this post, oh yeah, others may find it useful also ;).
chrome-9xf-Zagzel-B

If you found this post helpful, please give Kudos.
It gives me a sense of instant gratification and, if you give me Kudos enough times, magical unicorns will appear on your screen.
If you find my signature vaguely amusing, please give Kudos.
KIfp67uy-Sr
Proud to be a Super User!PBI-Super-User-Rank-30x30-1x

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.