Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
michalnrf
Frequent Visitor

Power Query steps

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.

pq-ex.png

1 ACCEPTED SOLUTION
latimeria
Solution Specialist
Solution Specialist

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"

 

latimeria_0-1672422457532.png

 

 

View solution in original post

1 REPLY 1
latimeria
Solution Specialist
Solution Specialist

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"

 

latimeria_0-1672422457532.png

 

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors