Power query, move rows to columns based on there value

To achieve this transformation in Power Query, you can follow these steps to pivot the landline numbers into separate columns (Landline1, Landline2), based on Company and Phone type:

1. Load your data into Power Query.
2. Filter to keep only rows where Phone type = "landline".
3. Add an Index column grouped by Company to distinguish multiple landlines.
4. Pivot the landline table with Index as column headers and number as values.
5. Rename columns to Landline1, Landline2, etc.
6. Merge the landline table back with the original data (outer join on Company).

Power Query M Script Example:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    FilterLandline = Table.SelectRows(Source, each [Phone type] = "landline"),
    AddIndex = Table.AddIndexColumn(FilterLandline, "Index", 1, 1, Int64.Type),
    Pivoted = Table.Pivot(Table.TransformColumnTypes(AddIndex, {{"Index", type text}}, "en-US"), 
                List.Distinct(Table.TransformColumnTypes(AddIndex, {{"Index", type text}}, "en-US")[Index]), 
                "Index", "number"),
    RenamedCols = Table.RenameColumns(Pivoted,{{"1", "Landline1"}, {"2", "Landline2"}}),
    RemovedPhoneType = Table.RemoveColumns(RenamedCols,{"Phone type"}),
    MergedTables = Table.NestedJoin(Source, {"Company"}, RemovedPhoneType, {"Company"}, "Landlines", JoinKind.LeftOuter),
    ExpandedLandlines = Table.ExpandTableColumn(MergedTables, "Landlines", {"Landline1", "Landline2"}),
    FinalTable = Table.SelectColumns(ExpandedLandlines, {"Company", "Phone type", "number", "Landline1", "Landline2"})
in
    FinalTable

This will give you a final output as shown in your screenshot — each row will show the mobile or landline type, and Landline1, Landline2 for each company.

Tips:

• If you may have more than 2 landlines in the future, consider dynamically handling more columns or using a custom column name pattern like "Landline" & Index.
• Use Microsoft Power Query M documentation to modify for your environment.