Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
Hello,
can you help me with steps how shoould i use to prepare data like in example below in power query?
1 case what i have, 2 case what i want.
Thanks a lot.
Solved! Go to Solution.
Hi @michalnrf ,
Try this:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bZDBCsQgDET/xXOhZsa/KT0KC3tYaBf299vYkkY3YNDBF2fMsqT9VetX0pTOlWUWmZEBFfCCJtbJNf0+27tuetJC2wNAL3hDIcC7m60CoNjzuhuAJzh8cPjgGIKjC87/4OiCi2UzgGab21R42TpBE11TP69hHAxtHVAeW3hbeFsMtiX87QmsBw==", 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]),
#"Replaced Value" = Table.ReplaceValue(Source,"","Worker",Replacer.ReplaceValue,{"Column2"}),
#"Grouped Rows" = Table.Group(#"Replaced Value", {"Column1"}, {{"Rows", each _, type table [Column1=nullable text, Column2=nullable text, Column3=nullable text, Column4=nullable text, Column5=nullable text]}}),
#"Promoted Headers" = Table.TransformColumns(#"Grouped Rows", {{"Rows", each Table.PromoteHeaders(_)}}),
#"Added Custom" = Table.AddColumn(#"Promoted Headers",
"Rows2",
each Table.RenameColumns([Rows],{[Column1], "Sheet"})),
#"Unpivoted Other Columns" = Table.TransformColumns(
#"Added Custom",
{
{"Rows2", each Table.UnpivotOtherColumns(_, {"Sheet", "Worker"}, "Date", "Data")
}
}
),
Expanded = Table.Combine(#"Unpivoted Other Columns"[Rows2]),
#"Reordered Columns" = Table.ReorderColumns(Expanded,{"Sheet", "Date", "Worker", "Data"}),
#"Sorted Rows" = Table.Sort(#"Reordered Columns",{{"Sheet", Order.Ascending}, {"Date", Order.Ascending}, {"Worker", Order.Ascending}})
in
#"Sorted Rows"
Hi @michalnrf ,
Try this:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bZDBCsQgDET/xXOhZsa/KT0KC3tYaBf299vYkkY3YNDBF2fMsqT9VetX0pTOlWUWmZEBFfCCJtbJNf0+27tuetJC2wNAL3hDIcC7m60CoNjzuhuAJzh8cPjgGIKjC87/4OiCi2UzgGab21R42TpBE11TP69hHAxtHVAeW3hbeFsMtiX87QmsBw==", 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]),
#"Replaced Value" = Table.ReplaceValue(Source,"","Worker",Replacer.ReplaceValue,{"Column2"}),
#"Grouped Rows" = Table.Group(#"Replaced Value", {"Column1"}, {{"Rows", each _, type table [Column1=nullable text, Column2=nullable text, Column3=nullable text, Column4=nullable text, Column5=nullable text]}}),
#"Promoted Headers" = Table.TransformColumns(#"Grouped Rows", {{"Rows", each Table.PromoteHeaders(_)}}),
#"Added Custom" = Table.AddColumn(#"Promoted Headers",
"Rows2",
each Table.RenameColumns([Rows],{[Column1], "Sheet"})),
#"Unpivoted Other Columns" = Table.TransformColumns(
#"Added Custom",
{
{"Rows2", each Table.UnpivotOtherColumns(_, {"Sheet", "Worker"}, "Date", "Data")
}
}
),
Expanded = Table.Combine(#"Unpivoted Other Columns"[Rows2]),
#"Reordered Columns" = Table.ReorderColumns(Expanded,{"Sheet", "Date", "Worker", "Data"}),
#"Sorted Rows" = Table.Sort(#"Reordered Columns",{{"Sheet", Order.Ascending}, {"Date", Order.Ascending}, {"Worker", Order.Ascending}})
in
#"Sorted Rows"
User | Count |
---|---|
17 | |
10 | |
9 | |
9 | |
8 |
User | Count |
---|---|
23 | |
18 | |
15 | |
13 | |
13 |