Hi everyone!I have a question that I'm trying to fix.I imported raw data from my CRM tool and when opening the columns, the Leads' lines were being duplicated as in the example below.The data I need is related to the last update of the lead status (second column).How can I make this filter? Is it in Power Query itself? I have to "group" the same rows from the first column and relate the first column to last update from second column.Because I have not found anywhere where I can filter and load on the dashboard only this last update of Lead, because in leads that are entering CRM, they should also pull only the last update on PowerBI Many thanks 🙂

Hi Anonymous , yo have to add a column to your existing table with the following formula:Table.Last( [Count] )

Filter by last modified on duplicate rows | Microsoft Fabric Community

13 Replies

amitchandak
Super User
5 years ago
ImkeF , can you help with this?
ImkeF
Community Champion
5 years ago
Hi Anonymous ,

no need to expand that column. Just extract the latest value with an added column with this formula:

List.Last( [Etapas] )

or List.Max, depending on the initial sort.
Anonymous
Not applicable
5 years ago
When I import the column, it give a List record...
Maybe that's the "problem"?
- V-lianl-msft
  Community Support
  5 years ago
  Hi Anonymous ,
  
  Please use the ‘Group by’ feature in ‘Edit Query’.
  
  Best regards,
  Liang
Anonymous
Not applicable
5 years ago
Didn't worked 😞
I can't solve this.
😣
ImkeF
Community Champion
5 years ago
Hi Anonymous ,

could you please paste more screenshots or the complete M-code you're using?

Otherwise I cannot help you any further here.

The code I've provided has a valid syntax - so there must be an issue with either your data or the way you have applied the code given.
- Anonymous
  Not applicable
  5 years ago
  Hi Imke!
  I basically want to do this below. I've founde a Index tutorial on YouTube and "kind of" solved.
  But I still can't show only the latest date...
  The problem is that I'm very newbie at M or other language...I came from digital marketing carreer (Google/Bing Ads, etc).
  
  Here's the code that PowerBi generated:
  
  let
  Source = Json.Document(Web.Contents("https://api.exactsales.com.br/v2/listarlead?max_results=2000", [Headers=[token_exact="MY_TOKEN-HIDE"]])),
  #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
  #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"id", "IntegracaoId", "Empresa", "DtCadastro", "DtAtualizacao", "Contatos", "Mercado", "Origem", "SubOrigem", "PreVendedor", "Grupo", "Vendedor", "TelEmpresa", "Tel2Empresa", "LinkPublico", "LinkMkt", "LinkFeedBack", "Site", "ProdutoLead", "Endereco", "Cidade", "Estado", "Pais", "Obs", "Etapa", "CpfCnpj", "Dores", "Produtos", "CamposPersonalizados", "Etapas"}, {"Column1.id", "Column1.IntegracaoId", "Column1.Empresa", "Column1.DtCadastro", "Column1.DtAtualizacao", "Column1.Contatos", "Column1.Mercado", "Column1.Origem", "Column1.SubOrigem", "Column1.PreVendedor", "Column1.Grupo", "Column1.Vendedor", "Column1.TelEmpresa", "Column1.Tel2Empresa", "Column1.LinkPublico", "Column1.LinkMkt", "Column1.LinkFeedBack", "Column1.Site", "Column1.ProdutoLead", "Column1.Endereco", "Column1.Cidade", "Column1.Estado", "Column1.Pais", "Column1.Obs", "Column1.Etapa", "Column1.CpfCnpj", "Column1.Dores", "Column1.Produtos", "Column1.CamposPersonalizados", "Column1.Etapas"}),
  #"Removed Other Columns" = Table.SelectColumns(#"Expanded Column1",{"Column1.Empresa", "Column1.DtCadastro", "Column1.DtAtualizacao", "Column1.Mercado", "Column1.Origem", "Column1.SubOrigem", "Column1.PreVendedor", "Column1.Grupo", "Column1.Vendedor", "Column1.Etapa", "Column1.CpfCnpj", "Column1.Dores", "Column1.Produtos", "Column1.CamposPersonalizados", "Column1.Etapas"}),
  #"Sorted Rows" = Table.Sort(#"Removed Other Columns",{{"Column1.Empresa", Order.Ascending}}),
  #"Expanded Column1.Mercado" = Table.ExpandRecordColumn(#"Sorted Rows", "Column1.Mercado", {"value"}, {"Column1.Mercado.value"}),
  #"Expanded Column1.Origem" = Table.ExpandRecordColumn(#"Expanded Column1.Mercado", "Column1.Origem", {"value"}, {"Column1.Origem.value"}),
  #"Expanded Column1.SubOrigem" = Table.ExpandRecordColumn(#"Expanded Column1.Origem", "Column1.SubOrigem", {"value"}, {"value"}),
  #"Expanded Column1.PreVendedor" = Table.ExpandRecordColumn(#"Expanded Column1.SubOrigem", "Column1.PreVendedor", {"Nome"}, {"Nome"}),
  #"Removed Other Columns1" = Table.SelectColumns(#"Expanded Column1.PreVendedor",{"Column1.Empresa", "Column1.DtCadastro", "Column1.DtAtualizacao", "Column1.Mercado.value", "Column1.Origem.value", "value", "Nome", "Column1.Vendedor", "Column1.Etapa", "Column1.CpfCnpj", "Column1.Dores", "Column1.Produtos", "Column1.CamposPersonalizados", "Column1.Etapas"}),
  #"Expanded Column1.Vendedor" = Table.ExpandRecordColumn(#"Removed Other Columns1", "Column1.Vendedor", {"Nome"}, {"Nome.1"}),
  #"Removed Other Columns2" = Table.SelectColumns(#"Expanded Column1.Vendedor",{"Column1.Empresa", "Column1.DtCadastro", "Column1.DtAtualizacao", "Column1.Mercado.value", "Column1.Origem.value", "value", "Nome", "Nome.1", "Column1.Etapa", "Column1.Etapas"}),
  #"Expanded Column1.Etapas" = Table.ExpandListColumn(#"Removed Other Columns2", "Column1.Etapas"),
  #"Expanded Column1.Etapas1" = Table.ExpandRecordColumn(#"Expanded Column1.Etapas", "Column1.Etapas", {"Etapa", "Qualificacao", "DtAvaliacao", "Pontuacao"}, {"Etapa", "Qualificacao", "DtAvaliacao", "Pontuacao"}),
  #"Changed Type" = Table.TransformColumnTypes(#"Expanded Column1.Etapas1",{{"DtAvaliacao", type datetime}}),
  #"Reordered Columns" = Table.ReorderColumns(#"Changed Type",{"Column1.Empresa", "DtAvaliacao", "Column1.DtCadastro", "Column1.DtAtualizacao", "Column1.Mercado.value", "Column1.Origem.value", "value", "Nome", "Nome.1", "Column1.Etapa", "Etapa", "Qualificacao", "Pontuacao"}),
  #"Changed Type1" = Table.TransformColumnTypes(#"Reordered Columns",{{"Column1.Empresa", type text}}),
  #"Added Index" = Table.AddIndexColumn(#"Changed Type1", "Index", 0, 1, Int64.Type),
  #"Reordered Columns1" = Table.ReorderColumns(#"Added Index",{"Column1.Empresa", "Index", "DtAvaliacao", "Column1.DtCadastro", "Column1.DtAtualizacao", "Column1.Mercado.value", "Column1.Origem.value", "value", "Nome", "Nome.1", "Column1.Etapa", "Etapa", "Qualificacao", "Pontuacao"}),
  #"Grouped Rows" = Table.Group(#"Reordered Columns1", {"Column1.Empresa"}, {{"Count", each _, type table [Column1.Empresa=nullable text, Index=number, DtAvaliacao=nullable datetime, Column1.DtCadastro=text, Column1.DtAtualizacao=text, Column1.Mercado.value=text, Column1.Origem.value=text, value=nullable text, Nome=text, Nome.1=nullable text, Column1.Etapa=text, Etapa=nullable text, Qualificacao=nullable text, Pontuacao=nullable text]}}),
  #"Renamed Columns" = Table.RenameColumns(#"Grouped Rows",{{"Column1.Empresa", "Column1.Company"}})
  in
  #"Renamed Columns"
ImkeF
Community Champion
5 years ago
Hi @rafaelbrunop ,

in your original thread you were asking about getting a value from a list.

Now you show a pic of a table.

That would of course be 2 different solution.

Unfortunately I cannot see the new pic clearly. Could you please paste a higher resolution?

thanks
- Anonymous
  Not applicable
  5 years ago
  Actually on that pic of List was a problem that I solved..lol
  
  So I want to show (or extract) only the latest line of the company that have various columns.
  Can you see clearly the image now? 🙂
Anonymous
Not applicable
5 years ago
Anyone? 😞
v-lionel-msft
Community Support
5 years ago
Hi Anonymous ,

The "Group by " feature can't help you ?

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
ImkeF
Community Champion
5 years ago
Hi Anonymous ,

yo have to add a column to your existing table with the following formula:
Table.Last( [Count] )
KayPreet
New Member
3 years ago
Hi, posting this to help anyone looking for another solution in future.
Below video gives simple solution:
https://www.youtube.com/watch?v=hidJ5T_DYQ0