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

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

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

      Thank you So much Sergei for your Support!!,   I recall similar issue you last time also solved by the function Pivotby , if you get some time to share the solution with pivotby too.

Resources