Copilot for Microsoft 365 Tech Accelerator
Feb 28 2024 07:00 AM - Feb 29 2024 10:30 AM (PST)
Microsoft Tech Community
SOLVED

[Power Query] Split rows into columns, but with some conditions

Copper Contributor

Hi, 

I want to split rows according to screenshot (sorry for limited description, but it's easier to show on graphic than describe the problem).

knyklu_0-1703691823046.png

In the table I have few rows like in "Input Data", and only what is unique is column "Nr_zlecenia"

Can Somebody help me?

 

4 Replies
best response confirmed by knyklu (Copper Contributor)
Solution

Hi @knyklu 

 

One way:

let
    Source = Excel.CurrentWorkbook(){[Name="Table"]}[Content],
    NullREP = Table.ReplaceValue(Source, each [REP], null,
        (x,y,z) as list => {null} & Text.Split(y, ";"),
        {"REP"}
    ),
    ExpandedRep = Table.ExpandListColumn(NullREP, "REP"),
    UpdatedEtap = Table.ReplaceValue(ExpandedRep, each [Etap], each [REP],
        (x,y,z) as nullable text => if z is null then y else null,
        {"Etap"}
    )
in
    UpdatedEtap

@L z. 

Many thanks. Works perfect as a part of big query.

Respect.

BR/Krzysiek

@knyklu 

You're welcome & Thanks for providing feedback

Hi @knyklu 

 

Better late than never :) On reflection the following is better:

let
    Source = Excel.CurrentWorkbook(){[Name="Table"]}[Content],
    NullRep = Table.ReplaceValue(Source, each [REP], null,
        (x,y,z) as list => {null} & Text.Split(y, ";"),
        {"REP"}
    ),
    ExpandedRep = Table.ExpandListColumn(NullRep, "REP"),
    UpdatedEtap = Table.ReplaceValue(ExpandedRep, each [REP], null,
        (x,y,z) as nullable text => if y is null then x else z,
        {"Etap"}
    )
in
    UpdatedEtap
1 best response

Accepted Solutions
best response confirmed by knyklu (Copper Contributor)
Solution

Hi @knyklu 

 

One way:

let
    Source = Excel.CurrentWorkbook(){[Name="Table"]}[Content],
    NullREP = Table.ReplaceValue(Source, each [REP], null,
        (x,y,z) as list => {null} & Text.Split(y, ";"),
        {"REP"}
    ),
    ExpandedRep = Table.ExpandListColumn(NullREP, "REP"),
    UpdatedEtap = Table.ReplaceValue(ExpandedRep, each [Etap], each [REP],
        (x,y,z) as nullable text => if z is null then y else null,
        {"Etap"}
    )
in
    UpdatedEtap

View solution in original post