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

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.

    • anupambit1797's avatar
      anupambit1797
      Iron Contributor
      Jun 15, 2025

      Sorry, SergeiBaklan​ , for I am taking more time to understand , and not able to grasp the concept.. what I did is as below(attaching the file again)

      Step 1) Created the measure, from the Table1 {Also , in Power pivot why we see two groups of the fields? one with the "File Name" "Inter band CA(num)" "Section Number" and the one with "Table1[File Name]" , "Table1[Inter band CA(num)]" and "Table1[Section Number]"?

      Step-2) Then I created a Measure like this

      Step-3), I can see the KPI( Measure) created in my Pivot as below, but when I select "Status" nothing happens

      Attaching my Worksheet again, 

      Br,

      Anupam

      • SergeiBaklan's avatar
        SergeiBaklan
        Diamond Contributor
        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

Resources