- Power Query
- Lookup Function. We get this function from this web site http://www.excelguru.ca/blog/2015/01/28/creating-a-vlookup-function-in-power-query/
- File excel for dictionary
So, we have a series of label, and want to look them up in this table (lookup table) in the dictionary.xlsx with two columns one for it-IT label and one for en-US label, see below
Now we will import into Power Query into model.xlsx file then territory's lookup table.
Now we will import into Power Query into model.xlsx file then product's table from SalesTerritory.csv file. The objective is change the label with a correct translation for examples SalesTerritoryRegion change in Region or in Italian language "Regione". After importing it we will can observed in power query the following structure:
Now we will create a query for perform the translation. The M formulas is:
let
Source = Csv.Document(File.Contents(PathFile & "\SalesTerritory.csv"),
[Delimiter=",", Columns=6, Encoding=1252, QuoteStyle=QuoteStyle.None]),
#"Removed Columns2" = Table.RemoveColumns(Source,{"Column2", "Column6"}),
#"Renamed Columns" = Table.RenameColumns(#"Removed Columns2",
{{"Column3", "Column2"}, {"Column4", "Column3"}, {"Column5", "Column4"}}),
#"Kept First Rows" = Table.FirstN(#"Renamed Columns",1),
#"Removed Top Rows" = Table.Skip(#"Renamed Columns",1),
#"Changed Type" = Table.TransformColumnTypes(#"Removed Top Rows",
{{"Column1", type number}}),
#"Transposed Table" = Table.Transpose(#"Kept First Rows"),
#"Invoked Custom Function" =
Table.AddColumn(#"Transposed Table", "lkpLabel", each lkpLabel([Column1],
lbTerritory, 2, false)),
#"Removed Columns" = Table.RemoveColumns(#"Invoked Custom Function",
{"Column1"}),
#"Transposed Table1" = Table.Transpose(#"Removed Columns"),
#"Table Combine" = Table.Combine({#"Transposed Table1",#"Removed Top Rows"}),
#"Promoted Headers" = Table.PromoteHeaders( #"Table Combine",
[PromoteAllScalars=true])
in
#"Promoted Headers"
Begin analyzing the formula from row 4 and 5. In this two row set two variables "Kept First Rows" e "Removed Top Rows" the first contains the first line where there are labels that I have to replacethe second contains the all line that they will need later
The First result ("Kept First Rows") is now ready to be transposed using the Table.Transpose() function
Here is the key step where the call the lkpLabel function for create a colum with the new translate label. This function use 4 parameters:
#"Invoked Custom Function" =
Table.AddColumn(#"Transposed Table", "lkpLabel", each lkpLabel
([Column1], lbTerritory, 2, false))
- Lookup_value => the value of the column which must be converted ([Column1])
- Table array => table array is the lookup table (lbTerritory)
- column = >number of column for lookup
- approximate_match => it's optional
and see bleow the new column (lkpLabel):
Once we got the new value, transpose the table again to restore the headers as a row
So now remains to connect the new header with the body of the table that contains all rows and that we previously saved to a variable "Removed Top Rows" . The table with the single header row needs to be combined with the original table with Table.Combine function
#"Table Combine" = Table.Combine({#"Transposed Table1",#"Removed Top Rows"})
Once the tables are combined, you can see the altered headers to promote the first row to headers using the Table.PromoteHeaders() function.
#"Promoted Headers" = Table.PromoteHeaders( #"Table Combine",
[PromoteAllScalars=true])
you can download the folder with all of the demo file from this link.
Nessun commento:
Posta un commento