Power BI for M&E dashboards

Get data and read the M that Power Query writes

Every dataset enters Power BI through Get data. For a CSV the steps are the same whether it came from KoboToolbox, an MIS export or a government portal.

1. On the Home ribbon choose Get data > Text/CSV and pick households.csv.
2. A preview opens. Do not press Load. Press Transform data, which opens the Power Query Editor.
3. Repeat for districts.csv. Both now appear under Queries on the left.

Look at the Applied steps pane on the right. Power Query has already taken three steps for you: Source, Promoted Headers and Changed Type. Open Home > Advanced Editor to see them as M. It will look much like this; your file path, and possibly the encoding number, will differ.

let
    Source = Csv.Document(File.Contents("C:\impactmojo\powerbi\households.csv"),
        [Delimiter = ",", Columns = 13, Encoding = 65001, QuoteStyle = QuoteStyle.None]),
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars = true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers", {
        {"hh_id", Int64.Type}, {"district", type text}, {"area", type text},
        {"caste", type text}, {"head_edu_years", Int64.Type}, {"hh_size", Int64.Type},
        {"monthly_pc_exp", Int64.Type}, {"land_acres", type number},
        {"has_toilet", type text}, {"has_bank_account", type text}})
in
    #"Changed Type"

How to read it

• An M query is a let ... in expression. Each line names a step and refers to the step before it. The name after in is what the query returns.
• A step name with a space is written #"Changed Type".
• Columns = 13 is fixed at the moment of import. If next month's export has a fourteenth column, the extra column is silently dropped. Delete Columns = 13, from that line if your exports change shape.
• The automatic type step guesses from the first rows. Check every column's icon in the header: 123 for whole numbers, 1.2 for decimals, ABC for text. An ID stored as a number loses its leading zeros, so a village code 0042 becomes 42. Set such columns to text.

The same import and type check in R, on the same file, runs here. Use it to confirm the row and column counts Power Query shows in its status bar.

Clean in Power Query: indicators, bands and a district table

Survey exports arrive with Yes/No text, raw years of schooling and stray spaces. Fix them in Power Query, once, so every measure downstream starts from clean columns. Each step below can be done with a menu, and the menu writes the M shown.

Yes/No to 1/0

Select the households query, then Add Column > Custom column. Name it toilet and enter the formula below. Repeat for bank.

#"Added toilet" = Table.AddColumn(#"Changed Type", "toilet",
    each if [has_toilet] = "Yes" then 1 else 0, Int64.Type),
#"Added bank" = Table.AddColumn(#"Added toilet", "bank",
    each if [has_bank_account] = "Yes" then 1 else 0, Int64.Type)

each means "for each row", and [has_toilet] is that row's value. The last argument sets the new column's type, so the column arrives as a whole number and not as type any.

Education bands

#"Added edu band" = Table.AddColumn(#"Added bank", "edu_band",
    each if [head_edu_years] = 0 then "None"
    else if [head_edu_years] <= 5 then "Primary (1-5)"
    else if [head_edu_years] <= 10 then "Secondary (6-10)"
    else "Higher (11+)", type text)

Tidy text keys before you join on them

District names typed by hand carry trailing spaces and mixed case, and "Gaya " will not match "Gaya" in a relationship. Select the district column in both queries and choose Transform > Format > Trim.

#"Trimmed district" = Table.TransformColumns(#"Added edu band", {{"district", Text.Trim, type text}})

A district summary with Group By

To build a separate one-row-per-district table (for export, or to check totals), right-click the households query, choose Reference, then Transform > Group By, Advanced.

let
    Source = households,
    Grouped = Table.Group(Source, {"district"}, {
        {"households", each Table.RowCount(_), Int64.Type},
        {"toilet_rate", each List.Average([toilet]), type number},
        {"mean_pc_exp", each List.Average([monthly_pc_exp]), type number}})
in
    Grouped

The R version of the indicator, the bands and the district table. Check the district counts against your Group By result.

The data model: one district, many households

A Power BI report is only as right as its model. The model for most survey dashboards is a star schema: one fact table with a row per unit observed (here, households), and dimension tables that describe something the facts belong to (here, districts). Facts carry numbers to add up; dimensions carry the labels you slice by.

1. Open Model view (the third icon on the left edge).
2. Power BI may already have drawn a line between the two tables on district. If not, drag district from districts onto district in households.
3. Double-click the line. Check that Cardinality reads Many to one (*:1) from households to districts, and Cross filter direction reads Single.

• Many to one says each household belongs to one district and each district has many households. If Power BI proposes many to many, the districts table has a duplicate district, which is a data problem. Fix it in Power Query before you go on.
• Single direction means a slicer on districts filters households, and not the other way. Leave it single unless you can say why you need both; two-way filters make totals hard to explain.
• Slice by the column on the one side. Put districts[state] and districts[district] in slicers, and hide households[district] (right-click, Hide in report view) so nobody slices by the wrong copy.

Wide indicator sheets: unpivot before you load

Programme teams often keep indicators one column per round: district, baseline, midline, endline. Power BI wants one column for the round and one for the value, so a single line chart can draw all three. Select district, then Transform > Unpivot Columns > Unpivot Other Columns.

#"Unpivoted" = Table.UnpivotOtherColumns(Source, {"district"}, "round", "value")

Table.UnpivotOtherColumns(table, pivotColumns, attributeColumn, valueColumn) keeps the columns you name and turns every other column into round/value pairs. "Other columns" matters: when an endline column is added next year, it is unpivoted too, with no change to the query.

In R, merge() does what the relationship does, and the all.x check tells you whether any household failed to find its district.

DAX measures, and why a rate is never a column

DAX gives you two ways to calculate. A calculated column is worked out once per row when the data loads, and stored. A measure is worked out when a visual asks for it, for whatever rows the reader's slicers leave in play. Indicators on a dashboard (rates, means, counts) are measures.

1. In Report view select the households table in the Data pane, then Home > New measure.
2. Type each measure below into the formula bar and press Enter. Each one is a separate measure.
3. Set the format on the Measure tools ribbon: percentage for the rate, whole number for the count.

Households = COUNTROWS ( households )

Toilet coverage = DIVIDE ( SUM ( households[toilet] ), [Households] )

Mean MPCE = AVERAGE ( households[monthly_pc_exp] )

Bank account rate = DIVIDE ( SUM ( households[bank] ), [Households] )

• DIVIDE(numerator, denominator [, alternateresult]) returns BLANK, or your alternate result, when the denominator is zero. With the / operator a district with no households would show an error in the middle of your dashboard.
• [Households] reuses a measure inside another. Define the denominator once and every rate that uses it stays consistent.
• Put the table name before the column (households[toilet]) and not before a measure ([Households]). That convention tells a reader at a glance which is which.

Why the rate must be a measure

Suppose you stored coverage as a column of group rates and let the total row average them. That gives every group equal weight, so a district of 20 households would count as much as one of 2,000. The measure above divides total toilets by total households for whatever is selected, so the total row is the true pooled rate.

In the course file every district has 24 households, so for districts the two answers happen to agree. Rural and urban households are unequal groups (164 and 76), and there they part company. The R cell shows both.

When a calculated column is right

Use a column for something you will slice by, which belongs to one row and does not change with the filters. An expenditure band is one.

MPCE band =
SWITCH (
    TRUE (),
    households[monthly_pc_exp] < 2000, "Under 2,000",
    households[monthly_pc_exp] < 4000, "2,000 to 3,999",
    "4,000 and above"
)

SWITCH(TRUE(), ...) tests each condition in order and returns the first that holds, so the bands cannot overlap.

CALCULATE and filter context

Every number in a visual is computed under a filter context: the set of filters from the row it sits in, the slicers on the page and any page or report filters. CALCULATE evaluates an expression under a filter context you change. It is the most used function in DAX and the one that explains almost every "why does my total look wrong" question.

Rural households = CALCULATE ( [Households], households[area] = "Rural" )

Rural toilet coverage = CALCULATE ( [Toilet coverage], households[area] = "Rural" )

SC/ST households =
CALCULATE ( [Households], households[caste] IN { "SC", "ST" } )

A filter argument such as households[area] = "Rural" replaces any filter already on that column. Put [Rural households] in a matrix by area and the Urban row also shows the rural count, because the measure overrides the row's own filter. Wrap the filter in KEEPFILTERS( ) when you want it to intersect with the row instead.

Share of the total

To show each district's share of all households, the denominator must ignore the district filter. REMOVEFILTERS (or the older ALL) clears it.

Share of households =
DIVIDE ( [Households], CALCULATE ( [Households], REMOVEFILTERS ( districts ) ) )

REMOVEFILTERS ( districts ) clears filters from every column of the districts table, so the share still sums to 100% when the reader also slices by state. A state slicer would still filter the numerator; decide whether your share is "of the state" or "of the whole sample", and write it into the visual title.

Reading the selection

Selected district title =
"Toilet coverage, " & SELECTEDVALUE ( districts[district], "all districts" )

SELECTEDVALUE returns the value when exactly one is selected and the alternate text otherwise. Use it as a card or as a visual title (Format pane, Title > fx) so a screenshot sent on WhatsApp still says what it shows.

The same three numbers in R, to check your cards against:

Population-weighted figures with SUMX

Mean MPCE from Module 5 averages over households: every household counts once, whether it has two members or nine. A per-person figure, the mean expenditure of the average person, weights each household by its size. DAX's iterators do this: SUMX(table, expression) evaluates the expression row by row and adds the results.

Persons = SUM ( households[hh_size] )

Mean MPCE per person =
DIVIDE (
    SUMX ( households, households[monthly_pc_exp] * households[hh_size] ),
    [Persons]
)

The same pattern takes any weight. If your file carries a survey weight column, as NFHS and PLFS unit-level files do, replace households[hh_size] with it in both places.

Check both means in R. If larger households here spend less per head, the weighted mean comes out lower, because those households now count once for each member.

Iterators and the total row

SUMX, AVERAGEX and their relatives loop over whatever rows the filter context leaves. That is why the total row of a matrix is computed afresh over all rows, never by adding up the rows above it. For a sum the two agree; for a mean or a rate they do not, and the fresh computation is the correct one.

A report people can read, and sharing it safely

A dashboard is read by a district officer on a phone, a programme manager on a projector and a donor in a PDF. Build for all three.

Page design

• Put the three or four headline measures as Card visuals across the top, each with a title that says the unit ("Households with a toilet, %").
• Use a bar chart, sorted by value, for comparing districts. Sort by clicking the visual's More options (...) > Sort axis. A pie chart with ten districts cannot be read.
• Keep slicers for state, area and caste group in one place on the page, and add the dynamic title from Module 6 so every visual says what selection it shows.
• Show the sample size. A card for [Households] beside the rates stops a reader trusting a rate computed on six households.

Accessibility

• Give every visual Alt text (Format pane, General > Alt text). A screen reader reads it in place of the chart. It accepts a DAX measure, so the alt text can state the current value.
• Set the keyboard order in View > Selection, on the Tab order tab, so it follows the reading order of the page.
• Never use colour alone to carry meaning. If red means "below target", say so in the label or a data label too.

Sharing: the one setting that can leak a dataset

To publish, choose Home > Publish and pick a workspace. Colleagues need access to that workspace or an app built from it, and both you and they need the licence your organisation's workspace requires. Schedule refresh in the service only when the source is somewhere the service can reach, such as SharePoint or a database; a CSV on your laptop cannot refresh on its own.

Version control

Because you saved as a Power BI project in Module 1, the model is stored as text. Put the project folder in a Git repository (the Git and Quarto course covers the commands) and commit after each working change. A diff then shows exactly which measure definition changed between two versions of a donor report. Keep raw survey files out of the repository; the .gitignore Power BI wrote keeps out its data cache, and you should add your data files to it as well.

Microsoft's own documentation

• Download Power BI Desktop, with the system requirements.
• Power Query M function reference and DAX function reference.
• Power BI Desktop projects (PBIP).
• Publish to web, including the warning quoted above.

Where next