Assigning different categories to (nearly) identical tables

[John_Doe3]

Greetings,

I will do my best to describe the problem:

I have a couple of excel sheets/tables from different trial locations, all containing pretty much the same columns / rows. I want to assign value "A" or value "B" to the data in these tables so I can filter for "A"or "B", and it will use only the data associated with either "A" or "B".

Example: 

Table 1 = A

Table 2 = B

Table 3 = B

Table 4 = A

When I select value A (by ways of slicer), I only want to see graphs derived from table 1 & table 4.

When I select value B, I only want to see graphs derived from table 2 & table 3.

I know it would be easier to add this in the excel, create a column and just load it with value A or B or something along those lines, but for reasons I won't go into, this is not an option. I need to be able to get the excel files the way they are and assign these labels to them with a transform somehow.

Is this doable? 

[MarkLaf · Accepted solution]

Yes, you can just use the Add Custom Column button at the end of each table query and assign A or B, then combine the tables.

A fancier way to do it would be to manually assign the categories in one place as rows [Category = "A/B", TableColumnToExpand = Table]. Example:

let
    // List of manual assignments
    // One row of { "Assigned Value", Table } for each Table
    TableAssignmentRows =
    { 
        { "A", Table1 }, 
        { "B", Table2 }, 
        { "B", Table3 }, 
        { "A", Table4 } 
    },

    NameOfGroupingColumn = "Category",

    AddAssignments = List.Transform( 
        TableAssignmentRows,
        (assignments)=> 
        Table.ReorderColumns( 
            Table.AddColumn( 
                assignments{1}, NameOfGroupingColumn, 
                each assignments{0}, type text 
            ), 
            {NameOfGroupingColumn} & Table.ColumnNames( assignments{1} ) 
        )
    ),

    Combine = Table.Combine( AddAssignments )
in
    Combine

[v-sdhruv]

Hi @John_Doe3  ,
Just wanted to check if you had the opportunity to review the suggestions provided?
If the 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-sdhruv]

Hi @John_Doe3  ,
Just wanted to check if you had the opportunity to review the suggestions provided?
Apologies @MarkLaf 
If the 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-sdhruv]

Hi @John_Doe3  ,
Just wanted to check if you had the opportunity to review the suggestions provided?
If the response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank You

[MarkLaf]

@v-sdhruv, I am not the OP.

[v-sdhruv · Accepted solution]

Hi @MarkLaf ,

1.Load all Excel tables (Table1, Table2, etc.) into Power BI as separate queries.

If its Same workbook or file (on different sheets):

• Go to Data tab → Click “Get Data” → “From Workbook”

• Select the file with your tables

• In the Navigator, select Table1 → Click Transform Data

• In Power Query, go to Add Column → Custom Column

• Name: GroupLabel 1
• Formula:= "A"         // or "B" depending on the source
• Click Close & Load To... → Only Create Connection

Repeat steps 1–5 for each table (Table2, Table3, etc.) assigning the correct label. 

2.  Combine the labeled tables: 

• Go to Data → Get Data → Combine Queries → Append

• In the Append dialog

  • Choose Append Queries as New

  • Select 3 or more tables (you’ll need to use the "Three or more tables" option if applicable)

This creates a new combined query that includes the  GroubLabel for each row.
3. Load to Excel

• Right-click the new appended query → Close & Load To...

• Choose to load it as a Table in a worksheet.

If the response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank You!

[MarkLaf · Accepted solution]

Yes, you can just use the Add Custom Column button at the end of each table query and assign A or B, then combine the tables.

A fancier way to do it would be to manually assign the categories in one place as rows [Category = "A/B", TableColumnToExpand = Table]. Example:

let
    // List of manual assignments
    // One row of { "Assigned Value", Table } for each Table
    TableAssignmentRows =
    { 
        { "A", Table1 }, 
        { "B", Table2 }, 
        { "B", Table3 }, 
        { "A", Table4 } 
    },

    NameOfGroupingColumn = "Category",

    AddAssignments = List.Transform( 
        TableAssignmentRows,
        (assignments)=> 
        Table.ReorderColumns( 
            Table.AddColumn( 
                assignments{1}, NameOfGroupingColumn, 
                each assignments{0}, type text 
            ), 
            {NameOfGroupingColumn} & Table.ColumnNames( assignments{1} ) 
        )
    ),

    Combine = Table.Combine( AddAssignments )
in
    Combine

[John_Doe3]

Hi MarkLaf,

Thank you for thinking along with me. However, it deserves mention that I'm not a coder by any stretch of the imagination and I would call myself a novice when it comes to Power BI / Excel. Where do I add the code you provided?