need help - Power query or M code solution needed

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.

 

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.