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
Jensej
Helper V
Helper V

Unpivot many columns

Hello there!

 

I need to normalize a table in Power BI but im not able to this do this on my own. 

 

bild.PNG

 

Can someone please help me step by step how to reach my goal. 

 

Here is a link with sample data. (Table Structure at the moment and how my goal would look like) https://easyupload.io/t116d1 

 

 

1 ACCEPTED SOLUTION

OK, got the data.

 

Here's the Advanced Editor code:  (for these types of problems, I do one Unpivot -> Split the attribute column to get column names -> Pivot)

 

let
    Source = Excel.Workbook(File.Contents("J:\data\powerbiForum\UntitledUnp.xls"), null, true),
    Sheet2 = Source{[Name="Sheet1"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Sheet2, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Date", type text}, {"ID", Int64.Type}, {"Article_1", type text}, {"Article_2", type text}, {"Article_3", type text}, {"Article_4", type text}, {"Amount_1", Int64.Type}, {"Amount_2", Int64.Type}, {"Amount_3", Int64.Type}, {"Amount_4", Int64.Type}, {"Price_1", Currency.Type}, {"Price_2", Currency.Type}, {"Price_3", Currency.Type}, {"Price_4", Currency.Type}, {"Minutes_1", Int64.Type}, {"Minutes_2", Int64.Type}, {"Minutes_3", Int64.Type}, {"Minutes_4", Int64.Type}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Date] <> null)),
    #"Replaced Value" = Table.ReplaceValue(#"Filtered Rows"," 0","",Replacer.ReplaceText,{"Date"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Replaced Value",{{"Date", type date}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type1", {"Date", "ID"}, "Attribute", "Value"),
    #"Filtered Rows1" = Table.SelectRows(#"Unpivoted Other Columns", each ([Value] <> "NULL")),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Filtered Rows1", "Attribute", Splitter.SplitTextByEachDelimiter({"_"}, QuoteStyle.Csv, true), {"Attribute.1", "Attribute.2"}),
    #"Pivoted Column" = Table.Pivot(#"Split Column by Delimiter", List.Distinct(#"Split Column by Delimiter"[Attribute.1]), "Attribute.1", "Value"),
    #"Sorted Rows" = Table.Sort(#"Pivoted Column",{{"Date", Order.Descending}, {"Article", Order.Ascending}}),
    #"Removed Columns" = Table.RemoveColumns(#"Sorted Rows",{"Attribute.2"})
in
    #"Removed Columns"

 

The column names and types might be different at your side.

 

Good luck

View solution in original post

7 REPLIES 7
HotChilli
Super User
Super User

Could you post your data on a different site please and i'll have a look.   Edge browser doesn't like easyupload.

Hi and thanks! This link better? https://file.io/JTUHAzKUZn0c 

Sorry, still not happy.  I think it's the file.   Can you virus check the file please?

OK, got the data.

 

Here's the Advanced Editor code:  (for these types of problems, I do one Unpivot -> Split the attribute column to get column names -> Pivot)

 

let
    Source = Excel.Workbook(File.Contents("J:\data\powerbiForum\UntitledUnp.xls"), null, true),
    Sheet2 = Source{[Name="Sheet1"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Sheet2, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Date", type text}, {"ID", Int64.Type}, {"Article_1", type text}, {"Article_2", type text}, {"Article_3", type text}, {"Article_4", type text}, {"Amount_1", Int64.Type}, {"Amount_2", Int64.Type}, {"Amount_3", Int64.Type}, {"Amount_4", Int64.Type}, {"Price_1", Currency.Type}, {"Price_2", Currency.Type}, {"Price_3", Currency.Type}, {"Price_4", Currency.Type}, {"Minutes_1", Int64.Type}, {"Minutes_2", Int64.Type}, {"Minutes_3", Int64.Type}, {"Minutes_4", Int64.Type}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Date] <> null)),
    #"Replaced Value" = Table.ReplaceValue(#"Filtered Rows"," 0","",Replacer.ReplaceText,{"Date"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Replaced Value",{{"Date", type date}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type1", {"Date", "ID"}, "Attribute", "Value"),
    #"Filtered Rows1" = Table.SelectRows(#"Unpivoted Other Columns", each ([Value] <> "NULL")),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Filtered Rows1", "Attribute", Splitter.SplitTextByEachDelimiter({"_"}, QuoteStyle.Csv, true), {"Attribute.1", "Attribute.2"}),
    #"Pivoted Column" = Table.Pivot(#"Split Column by Delimiter", List.Distinct(#"Split Column by Delimiter"[Attribute.1]), "Attribute.1", "Value"),
    #"Sorted Rows" = Table.Sort(#"Pivoted Column",{{"Date", Order.Descending}, {"Article", Order.Ascending}}),
    #"Removed Columns" = Table.RemoveColumns(#"Sorted Rows",{"Attribute.2"})
in
    #"Removed Columns"

 

The column names and types might be different at your side.

 

Good luck

amitchandak
Super User
Super User

@Jensej , You need multiple unpivot

https://kohera.be/blog/power-bi/how-to-unpivot-twice/

 

or New Table in DAX like

New Table =
Union(
summarize(Table, Table[Date], Table[ID], Table[AMOUNT_1], Table[Price_1], Table[MINUTES_1]),
summarize(Table, Table[Date], Table[ID], Table[AMOUNT_2], Table[Price_2], Table[MINUTES_2]),
summarize(Table, Table[Date], Table[ID], Table[AMOUNT_3], Table[Price_3], Table[MINUTES_3]),
summarize(Table, Table[Date], Table[ID], Table[AMOUNT_4], Table[Price_4], Table[MINUTES_4])
)

Tried this multiple pivot but it doesn't work. 

 

I selected all articles,amounts,price,minutes and then unpivot. First i got a error message and had to change type using locale on all columns and after that it worked.

After that i have two columns Attribute with (article_1,amount_1,price_1 and so on) Value with (7601,7601-A,3,1,45 and so on. 

Then i split Attribute column by position right 2 to get rid of all the _1, _2 and so on. I also deleted that column after. 

Then i had a Attribute column with article,price,amount,minutes and a column with values. I marked both and then choose pivot on value table but the end results is wrong.. .

 

 

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.