Excel Dataset Layout Help

[PPars2025]

This has been a problematic issue for me and I'm hoping someone can help me.  My Excel dataset contains a list of 31 employees, each with an audit date, audit score, job type and store number.  I have data for 2024 and 2025 and I'm wanting to use this with a drill through for my report.  My problem is, each employee will have multiple audits within each quarter of the year.  Below is an example of the headers of my Excel dataset table.  What options do I have to make the dataset compatible with a PBI table visualization?  Or is a table visualization even the right one to use?

Emp. Name/1st. Qtr. Audit Score/1st. Qtr. Audit Score/1st. Qtr. Store (#)/1st. Qtr. Job Type/2nd. Qtr. Audit Score/2nd. Qtr. Audit Score/2nd. Qtr. Store (#)/2nd. Qtr. Job Type

[v-priyankata]

Hi @PPars2025 
I hope this information is helpful. Please let me know if you have any further questions or if you'd like to discuss this further. If this answers your question, please Accept it as a solution and give it a 'Kudos' so others can find it easily.
Thank you.

[v-priyankata]

Hi @PPars2025 
I wanted to check if you had the opportunity to review the information provided by @Syk . Please feel free to contact us if you have any further questions. If response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank you.

[v-priyankata]

Hi @PPars2025 

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[PPars2025]

Yes absolutely and thank you:

[Syk]

From this, here's how I'd start...

1) Select your employee name and 2025 Store and unpivot other columns. This gives you an attribute column you can use to declutter your columns.

2) Add a conditional column to determine which quarter you're working with.

3) Split the Attribute column to get a list of attributes without the quarter in the name.

3) Pivot the list of attributes with "Value" as the value and don't summarize.
You should be left with something like this.

Here's the M for the steps I did (Source is just me pasting in data).

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("hY89CsMwDIWvUjwH25L8e4HSLqF78OChQyHNkt6fWjZJaGsoGFno6XviTZMY8/MOYhCAyijUaEp/XdZXnufSRWdZk9aX73hpaCB+THsbxLhW4H69bfUmCaZL8hRA7eD5sWTG0JiKEXUx2w4ibdztIjkfGV2TBF5AVeIW3VadZUTHVQLtTu5vOP8lFNu42Y7r6Ygaom/Hg0jpDQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Employee Name" = _t, #"1st. Qtr. Audit Date" = _t, #"1st. Qtr. Job Type" = _t, #"1st. Qtr. Store" = _t, #"1st. Qtr. Audit Score" = _t, #"2nd Qtr. Audit Date" = _t, #"2nd Qtr. Job Type" = _t, #"2025 Store" = _t, #"2nd Qtr. Audit Score" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Employee Name", type text}, {"1st. Qtr. Audit Date", type date}, {"1st. Qtr. Job Type", type text}, {"1st. Qtr. Store", Int64.Type}, {"1st. Qtr. Audit Score", type number}, {"2nd Qtr. Audit Date", type date}, {"2nd Qtr. Job Type", type text}, {"2025 Store", Int64.Type}, {"2nd Qtr. Audit Score", type number}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Employee Name", "2025 Store"}, "Attribute", "Value"),
    #"Added Conditional Column" = Table.AddColumn(#"Unpivoted Columns", "Quarter", each if Text.Contains([Attribute], "1st") then "Q1" else if Text.Contains([Attribute], "2nd") then "Q2" else if Text.Contains([Attribute], "3rd") then "Q3" else if Text.Contains([Attribute], "4th") then "Q4" else null),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Added Conditional Column", "Attribute", Splitter.SplitTextByDelimiter("Qtr.", QuoteStyle.Csv), {"Attribute.1", "Attribute.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Attribute.1", type text}, {"Attribute.2", type text}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"Attribute.1"}),
    #"Trimmed Text" = Table.TransformColumns(#"Removed Columns",{{"Attribute.2", Text.Trim, type text}}),
    #"Pivoted Column" = Table.Pivot(#"Trimmed Text", List.Distinct(#"Trimmed Text"[Attribute.2]), "Attribute.2", "Value")
in
    #"Pivoted Column"

[Syk]

Can you give an example of the data as well? You will most likely have to pivot the column headers but its hard to recommend anything without knowing what your data looks like.