The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredCompete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.
Hi guys, can someone help me out with the following problem?
I have a Excel table that has the month valued merged for 3 columns and I need it in the Total Cell.
What I Have in power query
Column1 | Column2 | Column3 | Column4 | Column5 | Column6 | Column7 | Column8 | Column9 |
null | null | null | Goal - SEP/20 | null | null | Goal - OCT/20 | null | null |
No. | Name | Function | Daily goal | Worked Day | Total | Daily goal | Worked Day | Total |
1 | Mary | A | 1 | 26 | 26 | 1 | null | 0 |
2 | John | A | 1 | 26 | 26 | 1 | null | 0 |
3 | Clau | B | 1 | 26 | 26 | 1 | null | 0 |
What Iam trying to achieve
Column1 | Column2 | Column3 | Column4 | Column5 | Column6 | Column7 | Column8 | Column9 |
null | null | null | Goal - SEP/20 | null | null | Goal - OCT/20 | null | null |
No. | Name | Function | Daily goal | Worked Day | Goal - SEP/20 | Daily goal | Worked Day | Goal - OCT/20 |
1 | Mary | A | 1 | 26 | 26 | 1 | null | 0 |
2 | John | A | 1 | 26 | 26 | 1 | null | 0 |
3 | Clau | B | 1 | 26 | 26 | 1 | null | 0 |
Any help is really appreciated
M.Penner
Solved! Go to Solution.
Try this:
let
Source = Table.FromRecords(Json.Document(Binary.Decompress(Binary.FromText("vZA9C8IwFEX/SshctU21H27aqiBYBQsO6hC0aDFNoDRDEf+7HbR9L3QUx9zcyzm845NGSuhCOnRKpRaCWp+AmYFrBuMmWCkuyIDsF7vTiNnt18Tsembgd+NtlKJxYHbDb/CyoG6ihsg24UWGbJdaXqpcSWQc81zU5Nagke1BlY/sSmJeI+dUVRxL9+yD/n3Y7pG3A503vKyR8wzKOsCQecCre/ig1Xc4G7EZZK/VXf6R7UJ2JLhG7PnP2ec3", BinaryEncoding.Base64),Compression.Deflate))),
transp = Table.Transpose(Source),
group = Table.Group(
transp,
"Column1",
{
"t",
(tbl)=> Table.ReplaceValue(
tbl,
each tbl[Column1]{0},
each [Column2]="Total",
(x,y,z)=>if z then y else x,
{"Column2"}
)
},
0,
(x,y)=>Byte.From(Text.StartsWith(y, "Goal"))
),
result = Table.Transpose(Table.Combine(group[t]))
in
result
Try this:
let
Source = Table.FromRecords(Json.Document(Binary.Decompress(Binary.FromText("vZA9C8IwFEX/SshctU21H27aqiBYBQsO6hC0aDFNoDRDEf+7HbR9L3QUx9zcyzm845NGSuhCOnRKpRaCWp+AmYFrBuMmWCkuyIDsF7vTiNnt18Tsembgd+NtlKJxYHbDb/CyoG6ihsg24UWGbJdaXqpcSWQc81zU5Nagke1BlY/sSmJeI+dUVRxL9+yD/n3Y7pG3A503vKyR8wzKOsCQecCre/ig1Xc4G7EZZK/VXf6R7UJ2JLhG7PnP2ec3", BinaryEncoding.Base64),Compression.Deflate))),
transp = Table.Transpose(Source),
group = Table.Group(
transp,
"Column1",
{
"t",
(tbl)=> Table.ReplaceValue(
tbl,
each tbl[Column1]{0},
each [Column2]="Total",
(x,y,z)=>if z then y else x,
{"Column2"}
)
},
0,
(x,y)=>Byte.From(Text.StartsWith(y, "Goal"))
),
result = Table.Transpose(Table.Combine(group[t]))
in
result
Hello @Mp1977
here some approach how to solve it.
Extract the first row as record and transform it to a table. Use only the filled value fields. Transform the column names by adding 2 to the number of the column. Join this record to you 2nd row and append the rest of your data table. Then promote the headers to have a good looking tabel 🙂
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WyivNyVHSQaPc8xNzFHQVgl0D9I0McMn6O4dgysbqRCv55esBuX6JualAyq00L7kkMz8PyHRJzMypVEgH6gZywvOLslNTFFwSK4GckPwSsCAhFSDTDYE838QikKAjEIO4RmYwwhDhHAOwaiMgyys/I4841cZAlnNOYimQciKgOhYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t, Column5 = _t, Column6 = _t, Column7 = _t, Column8 = _t, Column9 = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}, {"Column8", type text}, {"Column9", type text}}),
First= Record.ToTable( Table.First(#"Changed Type")),
#"Filtered Rows" = Table.SelectRows(First, each ([Value] <> "null")),
TransName = Table.TransformColumns
(
#"Filtered Rows",
{
{
"Name",
each "Column" & Text.From(Number.From(Text.Replace(_, "Column", ""))+2)
}
}
),
TableRecord = #"Changed Type"{1} & Record.FromTable(TransName),
Final = Table.PromoteHeaders(Table.Combine({Table.FromRecords({TableRecord}), Table.Range(#"Changed Type",2)}))
in
Final
Copy paste this code to the advanced editor in a new blank query to see how the solution works.
If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too
Have fun
Jimmy
Hello @Mp1977
do you have access to the file where this pivot-table is in? I always would reccomend you to change your database as possible and then read it as new database. You could put your goal-columns into rows and then read it. Or what is the goal of adapting a visual database into another visual database?
BR
Jimmy