Forum Discussion

anupambit1797's avatar
anupambit1797
Iron Contributor
Jun 11, 2025
Solved

How to get the details on the Headers from PDF

Dear Experts,                  In all Attached PDFs, in chapter 5, there's a section of inter-band CA, which I am interested in a format like below:- How to achieve this ? Thanks in Advance, ...
BI & Data Analysis
Formulas and Functions
power bi
  • SergeiBaklan's avatar
    SergeiBaklan
    Jun 15, 2025

    With data model we need

    1. first add another table with unique file names
    2.  Set relationships between it and main table

    3. Create new measure as

    Bands count:=COUNTA( Table1[Inter band CA(num)] ) + 0

    Please not, we plus zero to convert blanks to numbers

    4. Add KPI to that measure

    5. Create PivotTable using

    With that icons also will be in totals as well. 

    Perhaps it's easier to do everything with Power Query

    let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    DeclareType = Table.TransformColumnTypes(
        Source,
        {
              {"File Name", type text}
            , {"Section Number", type text}
            , {"Inter band CA(num)", type text}
        }),
    NameForEmpty = Table.ReplaceValue(
        DeclareType, "", "band",
        Replacer.ReplaceValue,{"Inter band CA(num)"}),
    CountBands = Table.Pivot(
        NameForEmpty,
        List.Distinct(NameForEmpty[#"Inter band CA(num)"]), "Inter band CA(num)",
        "Section Number", List.Count)
in
    CountBands

    pivoting inside. With that we have structured table to whic we may apply usual conditional formatting

anupambit1797's avatar
anupambit1797
Iron Contributor
Jun 12, 2025

Somehow I am able to achieve this 

but need to beautify a bit like if "1" then Green if nothing then red-cross.. while in this Conditional formatting , I am not able to use these = 1 and ="" condition.

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Jun 13, 2025

    Icon set conditional formatting doesn't work with blank cells, just ignores them. Was not able to download your file, thus on such sample

    Since you use PivotTable, you may create explicit measure and add KPI to it

    However, if you use main table only to create PivotTable, status also could be missed, in upper PivotTable combination of column t and row b. But if create separate dim table with values in C, create relationships and use for rows dim table, all works (bottom PivotTable)

    See in attached.

      • SergeiBaklan's avatar
        SergeiBaklan
        Diamond Contributor
        Jun 13, 2025

        KPI could be created only base on explicit measure. Most probably you use implicit measure, i.e. drag some field into Values section of PivotTable pane and select proper aggregation.

        Instead in Power Pivot create new measure, for it KPI shall be available.  Creating them you may use in PivotTable measure value, goal and status. Actually you need only Status shown as icons.

Resources