Forum Discussion
Mic1979
Post Partisan
Post PartisanCustom function with Table.TransformRows
Dear all, I have the sample data at the following link: https://docs.google.com/file/d/1N2JsMNBgqoMbvRK5p5hyrNl8i2KokzNZ/edit?usp=docslist_api&filetype=msexcel I have the following function...
Mic1979Post Partisan
Post Partisan1. OK
2. Yes I need to use this for different cases
3. Will try
Can I come back to you for the point 3. if I won't be able to make fine code?
Many thanks
dufoq3
Community Champion
Community ChampionFunction - you can cut out F function and paste it into a new query to make it usable for whole document
let
FileLink = Web.Contents("https://docs.google.com/uc?export=download&id=1yJGxTqikuKoGMwZEshory8ue6vmumcU-"),
ExcelWorkbook = Excel.Workbook(FileLink),
Source = Table.PromoteHeaders(Table.Skip(ExcelWorkbook{[Item="invoke",Kind="Sheet"]}[Data])),
F = (tbl as table, DO_col as text, DCP_col as text, DO_cond as list, DCP_cond as list)=>
Table.FromRecords(Table.TransformRows(tbl, each
if List.Contains(DO_cond, Record.Field(_, DO_col)) and List.Contains(DCP_cond, Record.Field(_, DCP_col))
then Record.TransformFields(_, {{DO_col, (x)=> "NPN / PNP"}, {DCP_col, (x)=> "NOT APPLICABLE"}})
else _ ), Value.Type(Table.FirstN(Source, 0)) ),
InvokedF = F(Source, "Digital_Output", "Digital_Communication_Protocol", {"NO OUTPUT", "condition2", "condition3..."}, {"IO Link", "condition2", "condition3..."})
in
InvokedF