Add query value to parameter (Power Query)

[cpirino]

I have this query and I need add the line that have the most caracteres quantity in a parameter dynamically.

Exemple: In this case the row 2 have more caracteres and I need to put the value in a parameter.

[cpirino]

I do this, but the value don't updated.

Follow the code of the query that I'm using the parameter.

let
    Fonte = Excel.Workbook(File.Contents("C:\Users\cpirino\OneDrive\Documentos\21. PLANILHA DE ABASTECIMENTO\1. PEDIDOS\BASES\PEDIDOS.xls"), null, true),
    Cubo1 = Fonte{[Name="Cubo"]}[Data],
    #"Tipo Alterado" = Table.TransformColumnTypes(Cubo1,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}, {"Column8", type text}, {"Column9", type text}, {"Column10", type text}, {"Column11", type text}, {"Column12", type text}, {"Column13", type text}, {"Column14", type text}, {"Column15", type text}, {"Column16", type text}, {"Column17", type text}, {"Column18", type text}, {"Column19", type text}, {"Column20", type text}, {"Column21", type text}, {"Column22", type text}, {"Column23", type text}, {"Column24", type text}, {"Column25", type text}, {"Column26", type text}, {"Column27", type text}}),
    #"Linhas Superiores Removidas" = Table.Skip(#"Tipo Alterado",3),
    #"Cabeçalhos Promovidos" = Table.PromoteHeaders(#"Linhas Superiores Removidas", [PromoteAllScalars=true]),
    #"Tipo Alterado1" = Table.TransformColumnTypes(#"Cabeçalhos Promovidos",{{"Situação Entrega", type text}, {"CD", Int64.Type}, {"Destino", type text}, {"Família", type text}, {"Departamento", type text}, {"Grupo", type text}, {"Sub-Grupo", type text}, {"Fornecedor", type text}, {"Pedido", Int64.Type}, {"Cód.Item", Int64.Type}, {"Local Apresentação", type text}, {"Descrição Item", type text}, {"Data Entrega", type date}, {"Semana Entrega", Int64.Type}, {"Distribuído/Loja", type text}, {"Venda", type number}, {"Verba Propaganda", type number}, {"Custo", type number}, {"Qtde", Int64.Type}, {"Saldo", Int64.Type}, {"Qtde Packs", Int64.Type}, {"Coordenado", Int64.Type}, {"Cluster", type text}, {"Profundidade", type text}, {"Tipo Grade", type text}, {"Modalidade", type text}, {"UF Destino", type text}}),
    #"Linhas Filtradas" = Table.SelectRows(#"Tipo Alterado1", each [Semana Entrega] = Semana),
    #"Colunas Removidas" = Table.RemoveColumns(#"Linhas Filtradas",{"Situação Entrega", "CD", "Destino", "Família", "Departamento", "Grupo", "Sub-Grupo", "Fornecedor", "Cód.Item", "Local Apresentação", "Descrição Item", "Data Entrega", "Semana Entrega", "Distribuído/Loja", "Venda", "Verba Propaganda", "Custo", "Qtde", "Saldo", "Qtde Packs", "Coordenado", "Cluster", "Profundidade", "Tipo Grade", "Modalidade"}),
    #"Coluna Condicional Adicionada" = Table.AddColumn(#"Colunas Removidas", "UF_Destino", each if [UF Destino] = "" then UF_Parameter else [UF Destino])
in
    #"Coluna Condicional Adicionada"

[cpirino · Accepted solution]

How I Am new in power query I didn't understand how worked a parameter correctly, but I founded that for use a value of the a query I need to make a drill down.

[cpirino]

Tanks adudani.

This code created a list and after I created a parameter with that value:

Now I duplicated the query UF Destino and a conditional collumn where I used the parameter.

But in the result only appers the current value, not the list value.

[adudani]

In Parameters on the ribbon select edit parameters, you should be able to select the value from the query to update the parameter value. 

We have to do this initially because the current value is required so you put Test which is filtered.

On data refresh it shouldn't be the case.

Please let me know if this is resolved.

If it isn't please provide additional information removing sensitive data.

Thanks

[adudani]

@cpirino ,

for getting the value with max length, paste the below code the advanced editor in a blank query:

Note: update the source:

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("hZHBCsIwDIZfRXoOkqZLbY9Fqwg6h3WCDI8evOhB3x+zCVphxUP5U/j4yN92nWqXk8Xl8bze7grUKYa9xHy3bUJ9kukYNm1UZ+jUPh5jLTMoQiKJ0DSb/qoBEYfD3k8Rx+kUtqmtVwOve97YIq8zO73tmsH6qkjndmQEYz2w+9jXtTQaWZ1JtGhK2NdK7MHNqjEwX9ZY7tctYZnP/IAhpXhII09LYKUNUiVtinSm/cvrn/ZghNZDuhL9tbOVX2NwlQNPb/v5BQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"(blank)" = _t, #"(blank).1" = _t, #"(blank).2" = _t, #"(blank).3" = _t]),
#"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"UF Destino", type text}, {"YEAR", Int64.Type}, {"COMPANY", type text}, {"VALUE", type number}}),
#"Removed Other Columns" = Table.SelectColumns(#"Changed Type",{"UF Destino"}),
#"Removed Duplicates" = Table.Distinct(#"Removed Other Columns"),
#"Text Length" = Table.AddColumn(#"Removed Duplicates", "Text Length", each Text.Length([UF Destino])),
#"Sorted Rows" = Table.Sort(#"Text Length",{{"Text Length", Order.Descending}}),
#"Kept First Rows" = Table.FirstN(#"Sorted Rows",1),
#"UF Destino" = #"Kept First Rows"[UF Destino]
in
#"UF Destino"

For the parameter:

Manage parameters -> new then the following:

this is for reference: https://www.youtube.com/watch?v=7TfV8qTUyfU

Have uploaded the solution here: https://1drv.ms/u/s!AkGuKJKxOlnAe4YRaZCmemBmw-8?e=avaNSZ

Appreciate a thumbs up if this is helpful.

Please accept this as the solution if your question is resolved.