表格内容行列转换问题

显示全部楼层 · 2021-2-19 01:13:03

大家好，请教一下，附件中的数据源能否通过PQ转换为“数据整理”表中的样式，表体内容和表头能对应上。谢谢

11222112038083.rar (12.35 KB, 下载次数: 0)

kyszg · 2021-2-19 01:18:03

11222112038084.rar (19.88 KB, 下载次数: 0)

2021-2-19 01:24:03

和楼上版主的思路基本相同，只不过我是利用各药品间的空行分组，供参考：

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("zZLdjtJQEMdfpeEataJs3AfQrBcmJl6SvTAbYohfZKP3XdYChS40Sy271CxFvroboQYVsHz4MHbmtFe8glOOsuDqhXcmvThn5n/+nfnNJBIRZpegPIpEI7dEMXYzLorbYmQ3mohA5QC0I4pD/yy4UKHfYpbOzodMt7FQ9WaVxTTvuSqdF1Nl+YIygaTTC7RG9Nr71iBj1OfQ6nLHggWjNn2+4q77YlmDssFNfbPITmeLqQr5LOtMmOuAasC8CvmPXMCdzoq8ZvG2eG0rHmc9XgGost+UKc4qw2UATxzQOoF8BP0aKJkgV8a8EZhD0Psr7XpW2MhdOnhjF5tSKCtnmNxlynnYgNZZSujI+1+RvBOLhRx/J/ng8ev91F5S2Lt/9543uYBTGzQHCrbvvKXfM/N4E+MleELelPBL8aoBq71h9icqDKXud+kAZIXZRbQa2GvSlfUb/sjBsYyfq78YYV4jtmS/RZDYtALZAQ4HJN6mO8ePVg5yWSr7KlfzWBDE67GnT27QcZUu1T23C+aMFA/3X75Kpl4Ij1LP08+SApuXwvhPDExRw77eH0LL4FdaIpCH3sSgJYr+Bb7fkNDVWM/EeptQ+E4BjR5ntpjW1gbB5fhO8Rsf/EOVJ7Fq/duU0jur/jfnsZagBaWwN2mHEYK/XPj0ziZgME2mD6DuslnVG3/Fk1I4oVbNz8z+vK3/P8fdHw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t]),
Grouped = Table.Group(
Source,
"Column1",
{"All", each Table.PromoteHeaders(Table.Transpose(Table.SelectRows(_, each [Column1]"")),[PromoteAllScalars=true])},
GroupKind.Local,
(s,c)=>Number.From(Text.Length(c)=0)
),
Final = Table.Combine(Grouped[All])
in
Final