英文:
Merge rows in Power Query to replace null values
问题
我有一个表格，其中行被重复，我需要将它们合并为一行。
在这个示例中，我已将其压缩到一月和二月，我需要合并，在我的实际数据中，每个月都有一列。
我可以在Excel中完成这个操作，但我想在Power Query 中完成，如果可能的话？
到目前为止，我已尝试使用分组和转置行，但要么出现错误，要么最终得到相同的结果，每个月一行。
英文:
I have a table where rows are duplicated and I need to merge these into one row.
In this example I have condensed it to January and February which I need to merge, in my actual data there is one column for each month.
I can do this in Excel but I would like to do it in Power Query instead, if possible?
So far I have tried to Group By and Transposing the rows but either I get an error or I end up back in the same results, one row per month.
答案1
得分: 0
你可以简单地在“一月”列上过滤“一月”，然后将[二月]列中的所有null值替换为“二月”，以获得预期的结果。所有其他数据都是重复的，除了月份编号，但你正在丢弃那些信息。
英文:
You can simply filter the [January] column on "January" and then replace all null values in the [February] column with "February" to get your expected result. All other data is duplicate anyhow, except for the MonthNr, but you are discarding that information.
答案2
得分: 0
尝试在源数据文件上执行以下操作。它对许多内容进行分组，然后填充月份列。
let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Grouped Rows" = Table.Group(Source, {"ContractNumber", "Year", "Quarter", "Product", "SerialNumber", "PersonFullName", "PersonCostCarrierCode", "CompanyName", "CompanyOrgNo","Cost"}, {{"data", each
Table.FirstN(Table.FillUp(
Table.SelectColumns(_,{"December", "November", "October", "September", "August", "June", "May", "April", "March", "February", "January"})
,{"January", "February", "March", "April", "May", "June", "August", "September", "October", "November", "December"}),1)
, type table }}),
#"Expanded data" = Table.ExpandTableColumn(#"Grouped Rows", "data", {"December", "November", "October", "September", "August", "June", "May", "April", "March", "February", "January"}, {"December", "November", "October", "September", "August", "June", "May", "April", "March", "February", "January"})
in #"Expanded data"
这是你提供的代码的翻译部分，没有其他内容。
英文:
Try this on your source data file. It groups on a bunch of stuff, then fills up the month columns
let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Grouped Rows" = Table.Group(Source, {"ContractNumber", "Year", "Quarter", "Product", "SerialNumber", "PersonFullName", "PersonCostCarrierCode", "CompanyName", "CompanyOrgNo","Cost"}, {{"data", each
Table.FirstN(Table.FillUp(
Table.SelectColumns(_,{"December", "November", "October", "September", "August", "June", "May", "April", "March", "February", "January"})
,{"January", "February", "March", "April", "May", "June", "August", "September", "October", "November", "December"}),1)
, type table }}),
#"Expanded data" = Table.ExpandTableColumn(#"Grouped Rows", "data", {"December", "November", "October", "September", "August", "June", "May", "April", "March", "February", "January"}, {"December", "November", "October", "September", "August", "June", "May", "April", "March", "February", "January"})
in #"Expanded data"
答案3
得分: 0
这是一种方法：
- 对“Months”列进行解析
- 完成后，Attributes和Values列将相同，因此删除其中一个
- 按定义“重复行”的列进行分组
- 在聚合中，创建一个月份数据的记录：<br>
例如：{"Records"，每个Record.FromList([Month]，[Month])，记录类型} - 然后展开该记录列，使用月份的列表作为列名
英文:
Here is one way of doing this:
- Unpivot the "Months" columns
- After doing this, the Attributes and Values columns will be identical, so delete one of them
- Group by the columns that define your "duplicate rows"
- in the Aggregation, create a Record of the months data:<br>
eg:{"Records", each Record.FromList([Month],[Month]), type record} - Then Expand that record column, using a List of the Months for the Column Names
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论