Power query, move rows to columns based on there value

[rafterse]

The data

[v-priyankata]

Hi @rafterse 
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 · Accepted solution]

Hi @rafterse 
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-priyankata]

Hi @rafterse 

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.

[ronrsnfld]

Perhaps a little different output than what you show.

And the code should adapt to any number of landlines or mobileLines per company.

Data

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WciwoyElV0lHyzU/KhDAqKioMlWJ1EFI5iXkpOZl5IKYPXNIvPzszEbu+4MTc4tK8dEKSWI3FKW2kFBsLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Company = _t, #"Phone type" = _t, number = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Company", type text}, {"Phone type", type text}, {"number", type text}}),

//Groub by Company and Phone Type
    #"Group Company/Phone" = Table.Group(#"Changed Type",{"Company"},{
        {"Numbers", (t)=>
            [a=Table.Group(t,"Phone type",{
                 {"idx", each Table.AddIndexColumn(_,"idx",1)}}),
             b=Table.ExpandTableColumn(a,"idx",{"number","idx"}),
             c=Table.TransformColumns(b,{"idx", each Text.From(_), type text}),
             d=Table.CombineColumns(c,{"Phone type","idx"},Combiner.CombineTextByDelimiter(".",QuoteStyle.None),"Phone type")
             ][d]}}),

//Expand and Pivot
    #"Expanded Numbers" = Table.ExpandTableColumn(#"Group Company/Phone", "Numbers", {"number", "Phone type"}),
    #"Pivoted Column" = Table.Pivot(#"Expanded Numbers", List.Distinct(#"Expanded Numbers"[#"Phone type"]), "Phone type", "number"),
    #"Set Data Type" = Table.TransformColumnTypes(#"Pivoted Column", 
        List.Transform(Table.ColumnNames(#"Pivoted Column"), each {_, type text}))
in
    #"Set Data Type"

Output 

[SolomonovAnton]

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.

✔️ If my message helped solve your issue, please mark it as Resolved!

👍 If it was helpful, consider giving it a Kudos!

[mromain]

Hello rafterse,

Here is a possible solution:

let
    Source = Table.TransformColumnTypes(Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WciwoyElV0lHyzU/KhDAqgEApVgchlZOYl5KTmQdi+sAl/fKzMxOx6wtOzC0uzUsnJInVWHzSsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Company = _t, #"Phone type" = _t, number = _t]), {{"Company", Any.Type}, {"Phone type", Any.Type}, {"number", Any.Type}}),
    DataLandlines = 
        let
            FilterLandLines = Table.SelectRows(Source, each ([Phone type] = "landline")),
            GroupByCompany = Table.Group(FilterLandLines, {"Company"}, {{"DataLandlines", each _[[Phone type], [number]], type table [Phone type=text, number=text]}}),
            fnTransformData = (t as table) as table =>
                let
                    AddColumnIndex = Table.AddIndexColumn(t, "Index", 1, 1, Int64.Type),
                    AddColumnName = Table.AddColumn(AddColumnIndex, "Name", each Text.Proper([Phone type]) & Text.From([Index])),
                    SelectColumns = Table.SelectColumns(AddColumnName,{"Name", "number"})
                in
                    SelectColumns,
            TransformDatalandLines = Table.TransformColumns(GroupByCompany, {{"DataLandlines", fnTransformData, type table}}),
            DevelopDataLandlines = Table.ExpandTableColumn(TransformDatalandLines, "DataLandlines", {"Name", "number"}, {"Name", "number"}),
            PivotColumnLandlines = Table.Pivot(DevelopDataLandlines, List.Distinct(DevelopDataLandlines[Name]), "Name", "number")
        in
            PivotColumnLandlines,
    MergeDataLandlines = Table.NestedJoin(Source, {"Company"}, DataLandlines, {"Company"}, "DataLandlines", JoinKind.LeftOuter),
    ExpandDataLandlines = let cols = List.Skip(Table.ColumnNames(DataLandlines)) in Table.ExpandTableColumn(MergeDataLandlines, "DataLandlines", cols, cols),
    ReplaceNullLandline1 = Table.ReplaceValue(ExpandDataLandlines,null,"No Landline",Replacer.ReplaceValue,{"Landline1"})
in
    ReplaceNullLandline1