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.
Hello,
the below query is not working: Column1 has several values and I added an Index column, for each row I'd like to calculate how many blank value are there in previous rows. Since this query is excecuted on several csv files in a folder I'd like to optimize it, I would like that the file is read only one time and buffered before using it several times: one for its normal shaping and one for each row calculating the count of previous blank row values.
Previous: I mean "with lower index values"
I think I made some mistake after "fx" and in the concept of buffering.
Many thanks
let
Source= Csv.Document(Parametro1,[Delimiter="=", Columns=2, Encoding=1252, QuoteStyle=QuoteStyle.None]),
buffer = Table.Buffer(Source),
#"Add index" = Table.AddIndexColumn(buffer, "Index", 0, 1, Int64.Type),
#"Function" = Table.AddColumn(#"Add index", "fx", each (Index)=> Table.RowCount(Table.SelectRows(#"Add index", each [Column1] = "" and [Indice] < Indice)),Int64.Type),
#"Removed column" = Table.RemoveColumns(#"Function",{"index"})
in
#"Removed column"
@ImkeF I know she's a Guru on M
Solved! Go to Solution.
another way to get the result
let
Origine = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WUtJRUorViVZKBDJSUksSM3MUSioLUhUMsYoaYRU1hoiiC5uAhZPQRE3BojBrk7Faiy5qhKInBaueVLx60jD1xAIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Colonna2 = _t, Colonna1 = _t]),
nrow=Table.RowCount(Origine),
ncol=Table.ColumnCount(Origine),
ir=Table.InsertRows(Origine,nrow,{Record.FromList(List.Repeat({""},ncol),Table.ColumnNames(Origine))}),
#"Aggiunta colonna indice" = Table.AddIndexColumn(ir, "Indice", 1, 1, Int64.Type),
#"Ordinate righe" = Table.Sort(#"Aggiunta colonna indice",{{"Colonna1", Order.Ascending}}),
#"Aggiunta colonna indice1" = Table.AddIndexColumn(#"Ordinate righe", "Indice.1", 0, 1, Int64.Type),
#"Ordinate righe1" = Table.Sort(#"Aggiunta colonna indice1",{{"Indice", Order.Ascending}}),
#"Aggiunta colonna personalizzata" = Table.AddColumn(#"Ordinate righe1", "idx", each if [Colonna2]="" then [Indice.1] else null),
ria = Table.FillUp(#"Aggiunta colonna personalizzata",{"idx"}),
trl=Table.RemoveLastN(ria,1),
#"Rimosse colonne" = Table.RemoveColumns(trl,{"Indice", "Indice.1"})
in
#"Rimosse colonne"
another way to get the result
let
Origine = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WUtJRUorViVZKBDJSUksSM3MUSioLUhUMsYoaYRU1hoiiC5uAhZPQRE3BojBrk7Faiy5qhKInBaueVLx60jD1xAIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Colonna2 = _t, Colonna1 = _t]),
nrow=Table.RowCount(Origine),
ncol=Table.ColumnCount(Origine),
ir=Table.InsertRows(Origine,nrow,{Record.FromList(List.Repeat({""},ncol),Table.ColumnNames(Origine))}),
#"Aggiunta colonna indice" = Table.AddIndexColumn(ir, "Indice", 1, 1, Int64.Type),
#"Ordinate righe" = Table.Sort(#"Aggiunta colonna indice",{{"Colonna1", Order.Ascending}}),
#"Aggiunta colonna indice1" = Table.AddIndexColumn(#"Ordinate righe", "Indice.1", 0, 1, Int64.Type),
#"Ordinate righe1" = Table.Sort(#"Aggiunta colonna indice1",{{"Indice", Order.Ascending}}),
#"Aggiunta colonna personalizzata" = Table.AddColumn(#"Ordinate righe1", "idx", each if [Colonna2]="" then [Indice.1] else null),
ria = Table.FillUp(#"Aggiunta colonna personalizzata",{"idx"}),
trl=Table.RemoveLastN(ria,1),
#"Rimosse colonne" = Table.RemoveColumns(trl,{"Indice", "Indice.1"})
in
#"Rimosse colonne"
This works and it's very efficient!
Ciao Rocco,
My M knowledgeis too basic to understand each step and I don't know why It seems on the right way, but it has some mistake if you have several non blank values at the beginning, I tried to add some heading rows at your source like in the image below.
Grazie
I'm sorry to say that it yet has a mistake when calculating on the first row if it's not blank.
I made this practical case as example:
let
Origine = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSlTSUUpJLUnMzFEoqSxIVTBUitXBFDXCKmoMEUUXNgELJ6GJmoJFgYJgOhmrteiiRih6UrDqScWrJw1TTywA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Colonna2 = _t, Colonna1 = _t]),
#"Raggruppate righe" = Table.Group(Origine, {"Colonna2"}, {{"all", each _}}, GroupKind.Local, (x,y)=>Number.From(y[Colonna2]="")),
#"Aggiunta colonna indice" = Table.AddIndexColumn(#"Raggruppate righe", "Indice", 1, 1, Int64.Type),
#"Rimosse colonne" = Table.RemoveColumns(#"Aggiunta colonna indice",{"Colonna2"}),
te = Table.ExpandTableColumn(#"Rimosse colonne", "all", {"Colonna1", "Colonna2"}, {"all.Colonna1", "all.Colonna2"}),
cpb={0}&(List.RemoveLastN(te[Indice],1)),
tfc=Table.FromColumns(Table.ToColumns(te)&{cpb},Table.ColumnNames(te)&{"NumberPreviuosBlankRows"}),
#"Rimosse colonne1" = Table.RemoveColumns(tfc,{"Indice"})
in
#"Rimosse colonne1"
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.