Forum Discussion

anandav's avatar
anandav
Icon for Skilled Sharer rankSkilled Sharer
6 years ago
Solved

How to dynamically add columns and fill down based on row value

Hi Experts,

I have the following data (there are more columns but simplified for discussion)

A 'block of data' will have only one Header_01, mulitple Header_02 (max 10 rows and usually consecuive)  and multiple Detail rows.

The data file can have muliple blocks of data.

 

Requirement:

I need to 'fill down' the Header_02 values of Column 1 and Column 2 (only these two column values) to Detail rows.

Optionally if I could 'fill up' where it is empty is good to have (in grey color).

But since there will be multiple Header_02 rows (max 10 rows and usually consecuive) how can I do this?

 

Thanks in advance.

Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    Try and test this scheme:

     

    let

    mt = Table.TransformColumnTypes(yourTab,{{"row type ", type text}, {"col1", Int64.Type}, {"col2", Int64.Type}}),
    nrows=Table.RowCount(mt),
    h2rows=Table.SelectRows(mt,each [#"row type "] = "h2"),
    howManyH2 = Table.RowCount(h2rows),
    colNames=List.Accumulate(List.Numbers(1,howManyH2),{{},{}},(s,c)=>{s{0}& {List.Repeat({h2rows{c-1}[col1]},nrows),List.Repeat({h2rows{c-1}[col2]},nrows)},s{1}&{"New" & Text.From(c)& "_Column1","New" & Text.From(c)& "_Column2"}}),
    tbl=Table.FromColumns(colNames{0},colNames{1}),
    #"Aggiunta colonna indice" = Table.AddIndexColumn(tbl, "Indice", 1, 1)

in
    #"Aggiunta colonna indice"

     

    let

    mt = Table.TransformColumnTypes(youTab,{{"row type ", type text}, {"col1", Int64.Type}, {"col2", Int64.Type}}),
    #"Aggiunta colonna indice" = Table.AddIndexColumn(mt, "Indice", 1, 1),
    #"Merge di query eseguito" = Table.NestedJoin(#"Aggiunta colonna indice",{"Indice"},#"Foglio1 (2)",{"Indice"},"Foglio1 (2)",JoinKind.LeftOuter),
    #"Tabella Foglio1 (2) espansa" = Table.ExpandTableColumn(#"Merge di query eseguito", "Foglio1 (2)", {"New1_Column1", "New1_Column2", "New2_Column1", "New2_Column2", "New3_Column1", "New3_Column2"}, {"New1_Column1", "New1_Column2", "New2_Column1", "New2_Column2", "New3_Column1", "New3_Column2"}),
    #"Rimosse colonne" = Table.RemoveColumns(#"Tabella Foglio1 (2) espansa",{"Indice"})
in
    #"Rimosse colonne"

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Try and test this scheme:

     

    let

    mt = Table.TransformColumnTypes(yourTab,{{"row type ", type text}, {"col1", Int64.Type}, {"col2", Int64.Type}}),
    nrows=Table.RowCount(mt),
    h2rows=Table.SelectRows(mt,each [#"row type "] = "h2"),
    howManyH2 = Table.RowCount(h2rows),
    colNames=List.Accumulate(List.Numbers(1,howManyH2),{{},{}},(s,c)=>{s{0}& {List.Repeat({h2rows{c-1}[col1]},nrows),List.Repeat({h2rows{c-1}[col2]},nrows)},s{1}&{"New" & Text.From(c)& "_Column1","New" & Text.From(c)& "_Column2"}}),
    tbl=Table.FromColumns(colNames{0},colNames{1}),
    #"Aggiunta colonna indice" = Table.AddIndexColumn(tbl, "Indice", 1, 1)

in
    #"Aggiunta colonna indice"

     

    let

    mt = Table.TransformColumnTypes(youTab,{{"row type ", type text}, {"col1", Int64.Type}, {"col2", Int64.Type}}),
    #"Aggiunta colonna indice" = Table.AddIndexColumn(mt, "Indice", 1, 1),
    #"Merge di query eseguito" = Table.NestedJoin(#"Aggiunta colonna indice",{"Indice"},#"Foglio1 (2)",{"Indice"},"Foglio1 (2)",JoinKind.LeftOuter),
    #"Tabella Foglio1 (2) espansa" = Table.ExpandTableColumn(#"Merge di query eseguito", "Foglio1 (2)", {"New1_Column1", "New1_Column2", "New2_Column1", "New2_Column2", "New3_Column1", "New3_Column2"}, {"New1_Column1", "New1_Column2", "New2_Column1", "New2_Column2", "New3_Column1", "New3_Column2"}),
    #"Rimosse colonne" = Table.RemoveColumns(#"Tabella Foglio1 (2) espansa",{"Indice"})
in
    #"Rimosse colonne"
    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      a solution, to be tested, without the use of the merge function:

       

       

       nrows=Table.RowCount(mt),
let 
   h2rows=Table.SelectRows(yourTab,each [#"row type "] = "h2"),
    howManyH2 = Table.RowCount(h2rows),
    colNames=List.Accumulate(List.Numbers(1,howManyH2),{},(s,c)=>s&{"New" & Text.From(c)& "_Column1","New" & Text.From(c)& "_Column2"}),
    addCols=List.Accumulate(List.Numbers(1,howManyH2*2),yourTab,
        (s,c)=>Table.AddColumn(s,colNames{c-1},each Table.Column(h2rows,"col"&Text.End(colNames{c-1},1)){Number.FromText(Text.Range(colNames{c-1},3,1))-1}))

in
    addCols

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

         

        forse in questa forma è più chiaro:

         

            nrows=Table.RowCount(mt),
    h2rows=Table.SelectRows(yourTab,each [#"row type "] = "h2"),
    howManyH2 = Table.RowCount(h2rows),

    colNames=List.Accumulate(List.Numbers(1,howManyH2),{},(s,c)=>s&{"New" & Text.From(c)& "_Column1","New" & Text.From(c)& "_Column2"}),
    colIdx=List.Accumulate(List.Numbers(1,howManyH2),{},(s,c)=>s&{"col1","col2"}),
    rowIdx=List.Accumulate(List.Numbers(1,howManyH2),{},(s,c)=>s&{c-1,c-1}),
    addCols=List.Accumulate(List.Numbers(1,howManyH2*2),yourTab,
        (s,c)=>Table.AddColumn(s,colNames{c-1},each Table.Column(h2rows,colIdx{c-1}){rowIdx{c-1}}))

in
    addCols

         

  • v-juanli-msft's avatar
    v-juanli-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    Hi anandav 

    If any of answers sloved your problem, could you kindly accept it as a solution to close this case and help the other members find it more quickly?
    If not, please feel free to let me know.
     
    Best Regards
    Maggie