Forum Discussion

knyklu's avatar
knyklu
Copper Contributor
Dec 27, 2023
Solved

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

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).

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

Can Somebody help me?

 

excel
Formulas and Functions
  • Lorenzo's avatar
    Lorenzo
    Dec 27, 2023

    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

4 Replies

  • Lorenzo's avatar
    Lorenzo
    Silver Contributor
    Jan 19, 2024

    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
  • Lorenzo's avatar
    Lorenzo
    Silver Contributor
    Dec 27, 2023

    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

Resources