Forum Discussion
help for beginner power bi
Hi,
I am new here. I am struggling with the requirement to create a simple bar chart and table. I attached excelsheet. Sheet 1 is my data and sheet 2 is expected visualization. Can help to create this in power bi.
I am struggling in transforming the data on pivot or unpivot.
Appreciate your help.
10 Replies
If you need the data available in table visual, you could do a quick thing in Power Query. The easiest thing to explain would be the following code:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtQ31DcyVNJRCijKTylNLnEEMkODXYCkoYGRAVQivSi1uFgpVidayYg05cZoyp3wKzfBotzTJQiXclOCyp3zcwtyUktSU8DqzbA4Hp96c2zq/XCrt0BT7wxkegVE4lRvSVB9QGpeSmZeOli1oQHxymMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [date = _t, product = _t, curr = _t, #"txn ref" = _t, status = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"date", type date}, {"product", type text}, {"curr", type text}, {"txn ref", Int64.Type}, {"status", type text}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"product", "curr", "status"}, {{"Count", each Table.RowCount(_), Int64.Type}}),
#"Pivoted Column" = Table.Pivot(#"Grouped Rows", List.Distinct(#"Grouped Rows"[status]), "status", "Count", List.Sum)
in
#"Pivoted Column"
Replace the source step with data you have and you should be good to go. Here is the end result:Code simply is running a group by operation on Product, Currency and Status, calculating the number of rows. Then it Pivots the Status column, using Count as values.
But if I pivot and group, then I can't create bar chart (status vs count).
So, you can leave your table as it is, and build the bar chart like it was presented already here. For the table visual put Product and Currency as details and 3 measures calculating count of items for dach Status.
Hi learner_23
Just take your values in x axis and Y axis and change the aggregation from Sum to count on Y axis value. I have taken your sample data. chec the screenshot belowYou dont have to do any transformation on your data. I've taken your data as it is.
you can see the result.
I hope I answered your question!how to show in a table, requirement is not matrix visual
ryan_mayu thanks. how can we show without matrix visual. I want to show in table visual.
it's better to show in a matrix, it will be more complicated to show in a table.
Sure, I’d be happy to guide you through the process of creating a bar chart and table in Power BI using your data. Here are the general steps:
Load Data: First, load your data into Power BI. You can do this by clicking on “Home” > “Get Data” > “Excel”. Navigate to your Excel file and select the sheet that contains your data.
Transform Data: If your data needs to be unpivoted, you can do this in the Power Query Editor. To open the Power Query Editor, click on “Home” > “Edit Queries”. Select the columns you want to unpivot, then right-click and select “Unpivot Columns”.
Create Bar Chart: Once your data is in the correct format, you can create a bar chart. Click on the “Bar chart” icon in the “Visualizations” pane. Then, drag the fields you want to use onto the “Values” and “Axis” areas.
Create Table: To create a table, click on the “Table” icon in the “Visualizations” pane. Then, drag the fields you want to include in the table onto the “Values” area.
Remember, the exact steps might vary depending on the specifics of your data and what you want your visuals to look like. If you can provide more details about your data and the issues you’re encountering, I might be able to provide more specific guidance. 😊
