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

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

ImkeF

How to provide sample data in the Power BI Forum

If you want to have your question answered quickly and avoid many back and forth, you should read this excellent article with lots of useful tips. One of them is providing sample data. To describe your problem, it is often very helpful if you make a screenshot of your table(s) and paint or annotate the logic you want to achieve. A picture is the perfect medium for it and everyone is allowed to upload those. However, in addition to that, sometimes the problem can best be solved by playing with the data or some sample data (if the original data is confidential or too large). There are some things to consider for doing so:

 

1) Uploading files

Especially when you have a problem with a DAX statement or the data model, it would be best to provide a pbix-file containing the sample data. You might have seen posts here in the forum where files are directly attached and have searched for the button to do exatly that. But unless you're a Microsoft employee or a super user this feature will not be available for you. Instead you have to upload your file to a cloud storage (like OneDrive, Dropbox, Google Drive or Wetransfer for example) and paste the link to that storage location in the post.

 

2) Pasting tables into the post

Another way is to paste table(s) with the sample data directly into the post. If you do that, it's important that this results in proper HTML-tables because otherwise it will become difficult to copy that data into the tools to work upon. The following steps show how easy it is:

 

2a) Only take data that you've copied from a spreadsheet

Please don't copy the data directly from Power BI to the post. This will most likely mess up the headers and other elements as well.

Also, if your data comes from another webpage or wherever: Copy it first into a spreadsheet.

Select the data in the spreadsheet and copy it there.

 

2b) Use the "Table"-button to insert your data.

 

image.png

 

 

If you don't see that button, that means that the current web page is not fully rendered yet. Wait a bit for it to appear.

 

2c) Select just ONE field in the table

Table --> Table -> select just ONE (!) field (irrespective of the size of the copied data)

 

image.png

 

2d) Paste the copied table content 

Place your mouse into the box:

 

image.png

 

 

paste with Ctrl + V (or Strg + V)

 

2e) Check the results

The header row should be identifiable as such:

 

image.png

 

But if your data looks like so, something went wrong:

 

image.png

 

This is probably due to some messed up HTML formatting. I would start a new thread to paste this data instead. Close the current thread and give a hint that further content is coming. Pasting into a fresh thread should be the quickest way going forward.

 

Please have in mind that you cannot post very large tables directly into the post, as the size is limited to 20.000 characters per post. (Counting the characters in the HTML-code).

 

Anything I've missed? Please let me know in the comments below.

Thanks and stay queryious 😉

Comments
Anonymous

Hey,

 

I am trying to substract the values based on one column to another column.

 

In the below table ID is having groups of Data,if the ID and the Cycle No is 1,and the faults are different the output should be 1.

 

If the Same ID and Cycle No are different faults are same then the output suould be substract with the same Cycle No (For Example ID:K09031 Cycle No:2 Fault: No then next Cycle No:6 same Fault: No then output should be 6-2=4 & ID:K09031 Cycle No:2 Fault: No then next Cycle No:8 same Fault: No then output should be 8-2=6)

 

If the Same ID and Cycle No are different faults are same then the output suould be substract with the same Cycle No (For Example ID:K11121 Cycle No:1 Fault: Damage then next Cycle No:3 same Fault: Damage then output should be 3-1=2 , ID:K11121 Cycle No:1 Fault: Damage then next Cycle No:5 same Fault: Damage then output should be 5-1=4, ID:K11121 Cycle No:1 Fault: Damage then next Cycle No:6 same Fault: Damage then output should be 6-1=5, ID:K11121 Cycle No:3 Fault: No then next Cycle No:6 same Fault: No then output should be 6-3=3 & ID:K11121 Cycle No:1 Fault: Edge then next Cycle No:6 same Fault: Edge then output should be 6-1=5)

 

Note: Here Id has indusival groups with cycle no's and different faults, that should substract with the same fault occurs in the same group with earlier cycle no has same fault occur.

 

An Example if my dataset is this with required Solution Column:

 

IdCycle NoFaultSolution Column

KL0002

1Edge1
KL00021Damage1
K090312Edge2
K090312No2
K090316No4
K090318With8
K090318No6
K111211Damage1
K111211Edge1
K111213

No

3
K111213Damage2
K111215Damage4
K111216With6
K111216Damage5
K111216No3
K111216Edge5
KG13661No1
KG55601With1
H152385Edge5
H156781No1
HG12341No1
HG12341Edge1
H085422With2

 

Anyone with some ideas on how to fix this? Manually is not an option as there are thousands of datapoints.

 

Thanks & Regards,

Dharani

 

Hi guys, i need help for a situation. Before going to the problem, please understand the background.

Here is the data of students in Table 1 who have discontinued their study from university. I want to know in which semester student disconnected their study. I have dates when they leave but issue is this data for different batches. So for example, if a student admit in fall 16 and discontinued in Feb 2019 then he left the university in 6th semester but if a student is admit in fall 21 and discontinued in Sep 2021, it will count 1st semester. I need an automatic process because this data is coming live from a system.

 

1.jpg

Anonymous

Holdinga report.jpg

Hi everyone,

      i have a table the table name is holdings with five coulums i want to group by the columns ( product name, investor name, currency).  Here I need to get the month-end values from the "Holding Amount column, and I also same as month end dates in the Date column. Could you please have anyone help me?

Hi 
i have question in power Bi, im working on hour compansation report, and there's agent already owe to the company hours and other are not, howvere they already working an extra hours, i need a fourmal in power Bi( Dax) or whatever its called to get the hours needs to be compansate for those who owe hours only not the rest of users, i tried IF Dax, however its not leading me to anything , heres below screenshot from the data

Shady_0-1681172658115.png

 

Juned_0-1693580367697.png

Hello, I am working with a small data set in power Bi where I have one date table as shown above. Since my Auto Intellegence is turned on, I am getting a hierarchy here that is awesome. This hierarchy includes year, quarter and month. 

Now I want to make a comparison any two years, or quarters, or months based on the dataset. That is why I created a comparison table, and I made the connection inactive. Then I used the DAX formula using All (to remove all filters from my main date table) and Userelationship between the comparison table and main date table. Then I calculated the sum of revenue for comparison. Everything is working fine, however when I was just comparing the revenue, I selected a slicer for my main date table, and another slicer for my comparison date table so that I can pick two different dates from these tables to compare. However, I am finding the following result :

Juned_1-1693581258159.png

I realized that when I just connect these two tables (Table1, and Comparison Calender), Immediately the hierarchy switched from Table1 to Com. Calender. Why is it switching? my comparison table now has the hierarchy (Year, Month, Quarter), but No hierarchy for my main date table named as (Table1). In the begining, main date table has the hierarchy, but now it has switched, Why? please help me out

Hey everyone!
I am experiencing issues using Power BI to create a map with multiple layers (state and county). It seems that when I click on a state bubble to explore the counties, bubbles outside the intended state appear, and there are inconsistencies in the location of the bubbles in the legend. When I hover over the bubble outside the correct state, the respective legend correctly states the correct state; however, the bubble is located in another state. I have already checked my database, and there are some data entries where there is no county (N/A), but there is a state. Even so, the bubble appears outside the state when drilling down into the county.

Please help!!! How can I fix this?

 

Hello everyone, I am trying my understanding of how to paste a table into the comment portion. I decided to add a question along side this test.
Below is a portion of a GDP dataset for several countries over a period of time, from 1960 to 2017. There are some missing GDP values for some countries at some given years. Loaded into the Power BI query editor, the missing values were represented as "null" values. How do I deal with these "null" values in the query editor, before I Close and Apply into the Report Editor? Thank you in advance.

Country NameCountry CodeIndicator NameIndicator Code1960196119621963196419651966196719681969197019711972
ArubaABWGDP (current US$)NY.GDP.MKTP.CD             
AfghanistanAFGGDP (current US$)NY.GDP.MKTP.CD5.38E+085.49E+085.47E+087.51E+088E+081.01E+091.4E+091.67E+091.37E+091.41E+091.75E+091.83E+091.6E+09
AngolaAGOGDP (current US$)NY.GDP.MKTP.CD             
AlbaniaALBGDP (current US$)NY.GDP.MKTP.CD             
AndorraANDGDP (current US$)NY.GDP.MKTP.CD          78619206894098201.13E+08
United Arab EmiratesAREGDP (current US$)NY.GDP.MKTP.CD             
ArgentinaARGGDP (current US$)NY.GDP.MKTP.CD  2.45E+101.83E+102.56E+102.83E+102.86E+102.43E+102.64E+103.13E+103.16E+103.33E+103.47E+10
ArmeniaARMGDP (current US$)NY.GDP.MKTP.CD             
American SamoaASMGDP (current US$)NY.GDP.MKTP.CD             
Antigua and BarbudaATGGDP (current US$)NY.GDP.MKTP.CD             
AustraliaAUSGDP (current US$)NY.GDP.MKTP.CD1.86E+101.96E+101.99E+102.15E+102.38E+102.59E+102.73E+103.04E+103.27E+103.66E+104.13E+104.51E+105.2E+10
AustriaAUTGDP (current US$)NY.GDP.MKTP.CD6.59E+097.31E+097.76E+098.37E+099.17E+099.99E+091.09E+101.16E+101.24E+101.36E+101.54E+101.79E+102.21E+10
AzerbaijanAZEGDP (current US$)NY.GDP.MKTP.CD             
BurundiBDIGDP (current US$)NY.GDP.MKTP.CD1.96E+082.03E+082.14E+082.33E+082.61E+081.59E+081.65E+081.78E+081.83E+081.9E+082.43E+082.53E+082.47E+08
BelgiumBELGDP (current US$)NY.GDP.MKTP.CD1.17E+101.24E+101.33E+101.43E+101.6E+101.74E+101.87E+102E+102.14E+102.37E+102.68E+103E+103.74E+10
BeninBENGDP (current US$)NY.GDP.MKTP.CD2.26E+082.36E+082.36E+082.54E+082.7E+082.9E+083.03E+083.06E+083.26E+083.31E+083.34E+083.35E+084.1E+08

Thanks you for reply

Table 1 REV

StnTokenRiderCard RiderTotal RiderRevenueSJT ISSSJT REFRJT ISSRJT REFFET ISSFET REFPET ISSPET REFSVP RELSVP ISSSVP REFTRP RELTRP ISSTRP REFCorrectedRunDateP Group
VER41324437117253850140000000000043625-Mar-24MQR
VER299002990862302707314300000000000313825-Mar-24PQR
VER06686681389000000000102603767325-Mar-24CLP
VER062621470000000000000006225-Mar-24NCMC

Table 2 TR

STNSJTRJT45TULTSVPTOKPASTOTGroupDate
VER36445501940924433MQR25-Mar-24
VER0014881790668668CLP25-Mar-24
VER00006206262NCMC25-Mar-24
VER2656458000311403114PQR25-Mar-24

Manigandan_0-1711695052575.png

 

2nd Table

Manigandan_2-1711695651681.png

 

 

 

But I wasn't able to see attachment option to attach my sample data files

Hello! 

 

Can someone help me on the below topic?

I am trying to mach the values in a new column in the Main "Customer Profile" table "Product" column to match the VFAL_ product_ list table that have 2 columns "Product" and "product family name" and populate the column with the product family name in the Customer Profile.

Maybe it is also a relationship issue?

 

Elyblog2009_1-1720383918377.png

 

 

Elyblog2009_0-1720383770456.png

Thank you!

Hi,I have a table with forecast values and actuals. The actuals are pulling through correctly, but the forecast values are only showing staff costs and overheads, leaving out all other expense types. I've checked, and there doesn't seem to be a filter applied anywhere.Is anyone able to advise?

Adaonwa_0-1723549190131.png

 

Hi I want to generate a series of week wise aging I am providing a dummy data .
Created date     Resolved Date Days Aging

03-01-24 13:2723-04-24 11:45110
05-01-24 3:2907-04-24 13:0593
08-01-24 12:3407-04-24 13:0590
08-01-24 13:22 254
08-01-24 16:4911-06-24 13:51154
08-01-24 17:0824-05-24 19:30137
11-01-24 15:0316-05-24 15:48126
15-01-24 13:1503-09-24 12:21231
17-01-24 16:0106-04-24 23:2680
23-01-24 12:1015-02-24 11:5022

 So I want a distribution on weekly basis for eg. lets take 3 jan 2024 the ticket has been created and resolved in april so until it get resolved it should be adding weeks on week until it gets resolved .I have researched about it a lot I am not able to find the correct formula.Kindly help would love to hear from you.

 

MahimaHulale_0-1726655727286.png

Hi I want to generate a series of week wise aging I am providing a dummy data.

 So I want a distribution on weekly basis for eg. lets take 3 jan 2024 the ticket has been created and resolved in april so until it get resolved it should be adding weeks on week until it gets resolved .I have researched about it a lot I am not able to find the correct formula.Kindly help would love to hear from you.