need help - Power query or M code solution needed

[ashwinkolte]

Hello .. Any help for the below problem is highly appreciated 

Please refer source data as given below 

1) The source is an excel sheet as shown above with blocks of data seperated by a blank row

2) The blocks have different number of columns . There are some common columns across blocks (e.g Monitor_group). However the position of common columns can differ from block to block

3) I want to align all common columns in one single column of the output table . The uncommon columns can appear after the common columns 

4) Important - The source exce sheet is an incremental one . Hence in the future there could be new blocks of data with new columns and/or different sequence of columns . The solution should be able to handle this 

5) The first column "Monitor" is already aligned however the values MAY not not be unique .

7) The solution should retain the existing rows .. Just want the common columns aligned . Blank rows may be removed 

6) A power query solution or a M code solution , both is fine

7) If possible please share the solution PBI file 

[v-kpoloju-msft]

Hi @ashwinkolte,
Thank you for reaching out to the Microsoft fabric community forum. Thank you @jgeddes, and @lbendlin, for your inputs on this issue.

After thoroughly reviewing the details you provided, I was able to reproduce the scenario, and it worked on my end. I have used it as sample data on my end and successfully implemented it.

I am also including .pbix file for your better understanding, please have a look into it:

If this post helps, then please give us ‘Kudos’ and consider Accept it as a solution to help the other members find it more quickly.

Thank you for using Microsoft Community Forum.

[ashwinkolte]

Hi @v-kpoloju-msft 

First of all thanks for responding 

I saw the PBIX file and M code . if I am not wrong this code is using hardcoded column names . What if there there come additional ones which would come in the future in the input ? Will this work ?

#"Replaced Value" = Table.ReplaceValue(#"Transposed Table","",null,Replacer.ReplaceValue,{"Column1"}),
#"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","",null,Replacer.ReplaceValue,{"Column2"}),
#"Replaced Value2" = Table.ReplaceValue(#"Replaced Value1","",null,Replacer.ReplaceValue,{"Column3"}),
#"Replaced Value3" = Table.ReplaceValue(#"Replaced Value2","",null,Replacer.ReplaceValue,{"Column4"}),
#"Transposed Table1" = Table.Transpose(#"Replaced Value3"),
#"Renamed Columns" = Table.RenameColumns(#"Transposed Table1",{{"Column1", "Monitor"}, {"Column4", "Monitor_group"}, {"Column2", "Usergroup"}, {"Column3", "Poller"}, {"Column5", "Contact"}}),
#"Filled Down" = Table.FillDown(#"Renamed Columns",{"Poller", "Column6"}),
#"Reordered Columns" = Table.ReorderColumns(#"Filled Down",{"Monitor", "Monitor_group", "Usergroup", "Poller", "Contact", "Column6"}),
#"Renamed Columns1" = Table.RenameColumns(#"Reordered Columns",{{"Column6", "Portnumber"}}),
#"Reordered Columns1" = Table.ReorderColumns(#"Renamed Columns1",{"Monitor", "Monitor_group", "Usergroup", "Poller", "Portnumber", "Contact"})
in
#"Reordered Columns1"

[v-kpoloju-msft]

Hi @ashwinkolte,
Thank you for your detailed observation. You are right to raise this concern.

The current M code relies on hardcoded column names such as "Column1", "Column2", etc. This method can lead to issues if the input file structure changes in the future (e.g., new columns are added or the column order changes), as the transformation steps may not function as expected or may even cause errors.

To make the ReplaceValue step future proof and apply it across all columns dynamically (including any that may be added later), you can use the code below. It will replace empty strings ("") with null across the entire table without needing to update the column list manually:

ReplaceBlanksWithNulls = Table.ReplaceValue(

    #"Transposed Table",

    "",

    null,

    Replacer.ReplaceValue,

    Table.ColumnNames(#"Transposed Table")

)

This ensures that any new columns added to the source data will automatically be included in the transformation no code changes needed.

If this post helps, then please give us ‘Kudos’ and consider Accept it as a solution to help the other members find it more quickly.

Thank you for using Microsoft Community Forum.

[ashwinkolte]

Hi @v-kpoloju-msft  If possible can you pls send me PBIX with the change you mentioned which will make it completely dynamic

[v-kpoloju-msft]

Hi @ashwinkolte,

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.

[v-kpoloju-msft]

Hi @ashwinkolte,

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

Thank you.

[ashwinkolte]

Hi @v-kpoloju-msft  I was looking at the PBIX you sent earlier. And if I am not mistaken you are starting with the below source data . If this understanding is correct then the source itself is not right . This source has all columns aligned e.g monitor_group1 , monitor_group2 etc . The problem to bigin with that these values (under monitor_group header for different blocks of data) are spread across columns which need to be aligned 

[v-kpoloju-msft]

Hi @ashwinkolte,

Apologies for late reply and Thank you for reviewing the PBIX file and identifying the issue. You are correct that the current source data is in a wide format, with columns such as Monitor_group1, Monitor_group2, etc., which complicates data alignment and modeling in Power BI.

To address this, we will normalize the dataset by unpivoting these repeated columns into a long format. This method adheres to Power BI best practices and will enhance scalability and flexibility in reporting.

The planned approach in Power Query is as follows: Open the Power Query Editor. Select the repeated group columns (e.g., Monitor_group1, Monitor_group2, etc.). Right-click and choose Unpivot Columns. This action will transform them into two columns: Attribute (e.g., Monitor_group1, Monitor_group2) and Value (the monitor group data itself).

We will then rename: Attribute to Monitor_group_type and Value to Monitor_group. We will apply the same process to other repeating sections (such as Contacts, Partnumbers, etc.) if necessary.

If this post helps, then please give us ‘Kudos’ and consider Accept it as a solution to help the other members find it more quickly.

Thank you for using Microsoft Community Forum.

[ashwinkolte]

Hi @v-kpoloju-msft  Thanks for responding . But again if we do the operations in power query which you mentioned above , then future new columns will not be addressed because power query will hardcode the coumn names which would be then exisitng. We want fully dynamic M code which identifies all existing columns at any point in time and align them except the first column offcourse , against which we want to align all remianing columns and thier values

[v-kpoloju-msft]

Hi @ashwinkolte,

Thank you for the clarification, it makes perfect sense. I appreciate your focus on ensuring the solution remains dynamic and scalable for future schema changes.

To meet your requirement, we can use a dynamic M code approach that unpivots all columns except the first one (assuming it is an identifier like "ID" or "Date"). This method ensures that any new columns added later will automatically be included in the unpivoting process without needing to modify the query.

let

    Source = Excel.CurrentWorkbook(){[Name="YourTableName"]}[Content],

    FirstColName = Table.ColumnNames(Source){0},

    Unpivoted = Table.UnpivotOtherColumns(Source, {FirstColName}, "Monitor_group_type", "Monitor_group")

in

    Unpivoted

Table.ColumnNames(Source){0} dynamically identifies the first column, which we exclude from unpivoting. Table.UnpivotOtherColumns will unpivot all remaining columns, regardless of how many or their names.

If this solution works for you, please give it a Kudos and consider marking it as Accepted as Solution so it can help others as well.

Thank you for being part of the Microsoft Community Forum.

[ashwinkolte]

@v-kpoloju-msft  This will not align all other columns properly . It will just create 2 colums  one of  attribute and one with values right 

[v-kpoloju-msft]

Hi @ashwinkolte,

You’re correct that using Unpivot Columns will convert the selected columns into two: one for the column headers (attributes) and one for the corresponding values. This method is particularly effective for data preparation in Power BI, where a long format is often more suitable for creating visuals and measures.

However, if your objective is to preserve the structure of the original columns and simply rearrange or realign the data, unpivoting might not be the appropriate tool.

Could you provide more details about your intended outcome for instance, how you want the final data to appear or what you plan to do with it? This will help us recommend the best approach.

If this post helps, then please give us ‘Kudos’ and consider Accept it as a solution to help the other members find it more quickly.

Thank you for using Microsoft Community Forum.

[ashwinkolte]

Hi @v-kpoloju-msft You are right . The objective is to simply realign the common columns except offcourse the very first one (Monitor) which is aready aligned .I should be able to report on Monitors for all its parameters (which Monitoring group is it in , which ports are being checked , what is the poller for this monitor etc) 

As you know all these paramters appear in different columns for diffferent datablocks with a blank column between them becuase  the datablocks are simple dump of different tables of a monitoring tool 

And the biggest problem is that in the future there could be a new monitor which has new parameters , or even an existing monitor can have additional new parameters .

So the M code should be fully dynamic to identify all parameters for a monitor , align the common ones as a column name with thier values followed by unique columns with thier values . 

In summary , We do not need to summarize anything  . Just align the common column headers with their values infront of the monitor maintaining the same number of rows overall

Hope this helps 

[v-kpoloju-msft]

Hi @ashwinkolte,

Thank you for the clarification.

You are correct. The goal is to dynamically realign parameters across various data blocks while keeping the "Monitor" column constant. We need a flexible and scalable approach to accommodate any potential new parameters or structural changes in the future.

Split the data into logical blocks using the blank columns as separators. Unpivot each data block, turning parameters into rows with their corresponding values. Combine all unpivoted blocks into a single table for a consistent structure: “Monitor | Parameter Name | Value”. Pivot this combined table so that each unique parameter becomes a column, aligned per Monitor.

This approach ensures:
Common parameters like "Port" or "Poller" are grouped under single columns. Unique or new parameters are automatically picked up and shown in their own columns. The number of rows (Monitors) remains the same no summarization or loss of granularity.

If this helps, please consider giving a Kudos or marking it as Accepted Solution to assist others in the community.

Thanks for using the Microsoft Community Forum!

[v-kpoloju-msft]

Hi @ashwinkolte,

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.

[v-kpoloju-msft]

Hi @ashwinkolte,

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my 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-kpoloju-msft · Accepted solution]

Hi @ashwinkolte,

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.

[ashwinkolte]

Done. Thanks so much for your help

[ashwinkolte]

Hi @v-kpoloju-msft  

I was out of town for last few days . Let me try your solution over the weekend . Will definately get back to you .

Thanks for following up !

[jgeddes]

GroupKind.Local should be able to help you here.

Create a placeholder column that indicates the blank rows as null else a common value.

From there you can group on the placeholder column, choose no aggregation. You will need to add GroupKind.Local as the fifth variable in the Table.Group() function.

Filter out the null rows.

Promote the headers in the nested tables.

Remove the outer placeholder column.

Expand the desired columns.

let
    Source = 
    #table(
        {"Column1", "Column2", "Column3", "Column4", "Column5", "Column6"},
        {
            {"Monitor", "Usergroup", "Monitor_group", "Contact", "", ""},
            {"Monitor1", "Usergroup1", "Monitor_group1", "Contact1", "", ""},
            {"Monitor2", "Usergroup2", "Monitor_group2", "Contact2", "", ""},
            {"","","","","",""},
            {"Monitor", "Poller", "Contact", "Monitor_group", "Usergroup", ""},
            {"Monitor3", "Poller3", "Contact3", "Monitor_group3", "Usergroup3", ""},
            {"Monitor4", "Poller4", "Contact4", "Monitor_group4", "Usergroup4", ""},
            {"","","","","",""},
            {"Monitor", "Contact", "Usergroup", "Poller", "Monitor_group", "Portnumber"},
            {"Monitor5", "Contact5", "Usergroup5", "Poller4", "Monitor_group5", "Port5"},
            {"Monitor6", "Contact6", "Usergroup6", "Poller5", "Monitor_group6", "Port6"}
        }
    ),
    add_local_group_column = 
    Table.AddColumn(
        Source, 
        "placeholder", 
        each if [Column1] = "" then null else "G", 
        type text
    ),
    local_group = 
    Table.Group(
        add_local_group_column, 
        {"placeholder"}, 
        {
            {"AllRows", each Table.RemoveColumns(_, {"placeholder"}), type table}
        }, 
        GroupKind.Local
    ),
    remove_null_rows = 
    Table.SelectRows(
        local_group, 
        each ([placeholder] = "G")
    ),
    promote_nested_headers = 
    Table.TransformColumns(
        remove_null_rows, 
        {
            {"AllRows", each Table.PromoteHeaders(_, [PromoteAllScalars = true])}
        }
    ),
    remove_placeholder = 
    Table.RemoveColumns(
        promote_nested_headers,
        {"placeholder"}
    ),
    expand_desired = 
    Table.ExpandTableColumn(
        remove_placeholder, 
        "AllRows", 
        {"Monitor", "Usergroup", "Monitor_group", "Contact", "Poller", "Portnumber"}
    )
in
    expand_desired

  Hope this helps.