在自定义列中手动插入文本，并且在刷新报告时应该可见

问题描述

我们有一个表格，显示OpprId，收入，区域区域等数据，其中表格是根据最高收入进行排序的.该表是使用电源查询构建的.用户在此处想要添加一个新列并将其设置为状态列并保存状态.刷新报告连接后，此状态应该可见.因此，是否有任何自定义列公式可保存输入的文本，并且此状态应与OppId一起保存在接收器中，因为将来可能会添加新的OppId，以便更改排序顺序.

We have a table showing data like OpprId, revenue, area region where table is sorted based on highest revenue. This table was built using power query. Here user wants to add a new column and make it as status column and save the status. This status should be visible on refresh of the report connection. So is there any custom column formula to save the entered text, and this status should be in sink with OppId as in future new OppId's may be added so the sort order may change.

OppId   ServiceLineServiceRevenueCUS    Status 
1       101584729.9                     Good
2       62272199                        let u know
3       11000000                        dfghd
4       9000000                         fdgdf
5       8200000                         fdgf 
6       7500000                         fgdf 
7       6000000                         fgdf
8       5650000 

推荐答案

编辑2016年11月29日:a 视频现在可以用来阐明该解决方案.该代码与下面的代码略有不同；基本上还是一样的解决方案.

Edit 29/11/2016: a video is now available to clarify this solution.The code is a little bit different from the code below; basically it's still the same solution.

在另一个论坛上，我回答了类似的问题.首先，由Power Query查询读取并输出输入数据(名字和姓氏).一列被添加到该输出(年龄-手动维护).现在，我创建了一个查询，该查询从Input表读取数据，将其与Output表左连接，然后将结果写回到Output表.因此，Output表既是该查询的输入，又是该查询的输出.

On another forum I answered a similar question.First the input data (first name and last name) was read and output by a Power Query query.A column was added to that output (Age - manually maintained).Now I created a query that reads data from the Input table, left joins it with the Output table and write the results back to the Output table.So the Output table is both input and output from that query.

let
    Source1 = Excel.CurrentWorkbook(){[Name="Input"]}[Content],
    Typed1 = Table.TransformColumnTypes(Source1,{{"Last Name", type text}, {"First Name", type text}}),
    Source2 = Excel.CurrentWorkbook(){[Name="Output"]}[Content],
    Typed2 = Table.TransformColumnTypes(Source2,{{"Last Name", type text}, {"First Name", type text}, {"Age" , type number}}),
    Renamed2 = Table.RenameColumns(Typed2,{{"Last Name", "Last"}, {"First Name", "First"}}),
    Join1and2 = Table.Join(Typed1,{"Last Name", "First Name"},Renamed2,{"Last", "First"}, JoinKind.LeftOuter),
    Removed = Table.RemoveColumns(Join1and2,{"Last", "First"})
in
    Removed

这篇关于在自定义列中手动插入文本，并且在刷新报告时应该可见的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！