Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to Solution.
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"
Hi @anandav
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"
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
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.