Editing MSPowerBI (section)

==Operation==

===Import Data from Excel===
====General Information====
Power Query Editor always works with a preview of the data, to make the development process fast. When you load the data in Power BI, transformations will be applied on the entire dataset. Hence errors might have not been identified before the full import of data.


====Steps====
=====Define Import=====
* select import and file
* select transform (not load)
=====Delete Rows e.g. Header=====
* delete rows above headers (e.g. filter sums) by [[File:PowerBI_3.PNG|400px]]
* set first row as header line by [[File:PowerBI_4.PNG|400px]]
=====Add Columns=====
* see https://docs.microsoft.com/de-de/power-bi/transform-model/desktop-tutorial-create-calculated-columns
* [[File:PowerBI_11.PNG|400px]]
* example [[File:PowerBI_12.PNG|400px]]
<code>= Table.AddColumn(#"Geänderter Typ", "Charge Fact", each if Value.Is([#"Chargeability (FTE)#(lf)"], type number) then [#"Chargeability (FTE)#(lf)"] else 1)</code>


<code>= Table.AddColumn(#"Geänderter Typ2", "Activity Type", each if [#"Partner-PSP-Element"] = "9914.P10048.004" then "ES Admin" else if [#"Partner-PSP-Element"] = "9914.P10048.005" then "ES Training" else if [#"Partner-PSP-Element"] = "9914.P10048.006" then "ES Relationship Management" else "tbd")</code>


<code>= Table.AddColumn(#"Renamed Columns1", "Initials", each Text.Start([First Name], 1) & Text.Start([Last Name], 1))</code>

=====Delete Rows=====
* filter specific value (click to right down arrow in the column header and define the filter)
=====N charactors from left=====
* [[File:PowerBI_7.PNG|200px]]
* next step delete the rows to the right
* rename column to the original name (w/o .1 suffix)

=====Adjust Query=====
* Show Query Settings [[:File:Screenshot 2024-04-21 151120.png]]
* [[File:PowerBI_6.PNG|100px]]

=====Not summing up a column like personnel number=====
* [[File:PowerBI_10.PNG|400px]]

=====Error Handling=====
* [https://radacad.com/exception-reporting-in-power-bi-catch-the-error-rows-in-power-query example see here]
* remove all rows with an error in one column [[File:Screenshot 2024-04-22 143455.png|400px]] [[File:Screenshot 2024-04-22 143615.png|400px]]
* remove all rows with an error in any column [[File:Screenshot 2024-04-22 143919.png|400px]]
* replace errors in one column -> Replace Errors [[File:Screenshot 2024-04-22 144133.png|400px]]

===Export Data to Excel===
* move or right click in header
* select dots [[File:PowerBI_0.JPG|400px]]
* select export data [[File:PowerBI_1.JPG|400px]]


===Relationsships===
====Change Default Summarization====
* [[File:PowerBI_18.PNG|600px]]


===Visuals===

====General Properties====
* border [[File:PowerBI_31.PNG|200px]]

====Filter on Visuals====
* https://www.youtube.com/watch?v=8y4yjPYe4-8

====Show Selection Pane for Grouping====
* [[File:PowerBI_19.PNG|400px]]
* you need to select at least 2 visuals to have the context menu 'group'
* to avoid overlapping visuals put them in different groups

====Sorting according to Values====
* Enter Data and give the table a name [[File:PowerBI_14.PNG|200px]]
** if needed edit the data by [[File:PowerBI_15.PNG|200px]] which opens Power Query
** select [[File:PowerBI_16.PNG|400px]]
** creating columns and enter data
* Create Relationship
* Create new Sort Column with e.g. [[File:PowerBI_25.PNG|200px]]
 ES Activity Order = RELATED(ES_Activity_Order[ES Activity Order])
* set this column as sort column by [[File:PowerBI_26.PNG|100px]] [[File:PowerBI_17.PNG|600px]]

====Slicer====
* [[File:PowerBI_8.PNG|40px]]
* Selection Behavior [[File:PowerBI_9.PNG|200px]]

====Matrix====
* is neary the same as pivot
* https://www.goodly.co.in/create-pivot-table-in-power-bi/
* grand total and subtotals [[File:PowerBI_13.PNG|200px]]

====Mulit-Row Card====
* Title On/Off: caption of multi-row card on top
* Visual Data -> Fields -> Right Click -> Rename for this visual: text below number
* Visual Data -> Category Label On/Off: show text below number
* Visual Data -> Category Label: format of text below number

====Pie Chart====
* sequence of fields defines the position, starts at 00:00 
* Format -> Visual -> Legend -> Position
* Format -> Visual -> Slices -> Colors [[File:PowerBI_30.PNG|100px]]

====Single Card====
* 'Collout value' -> Font: size of number
* 'Category label' on/off: text below number
* 'Title': text above number

====Stacked Bar Chart====
* figures in bar by switching on 'data label'
* bar char width by spacing -> Inner Padding[[File:PowerBI_22.PNG|200px]]
* colors [[File:PowerBI_27.PNG|200px]]

====100% Stacked Column Chart====
* zoom slider [[File:PowerBI_23.PNG|200px]] [[File:PowerBI_24.PNG|50px]]

====Stacked Column Chart====
* show 0 values

=====Table=====

=====Show Duplicates=====
* https://www.goodly.co.in/find-duplicate-values-dimension-table-powerbi/
* [[File:PowerBI_21.PNG|400px]]