Hi i want my table to look like this , i know that we will use Unpivot columns , but i tried it and no sccuess . Name Line Material Name Quanity Row Pallets Leakna 2Coke 1234Dara 1Mutant 1012Leakna 2Fanta 1035Dara 1Thunder1223Leakna 2Sprite1035Dara 1Juice1211 Below is my originated table , how to create it like the table above Name Line Material Name Quanity Row Pallets Material Name 1Quanity 1Row 1Pallets 1Material Name 2Quanity 2Row 2Pallets 2Leakna 2Coke 1234Fanta 1035Sprite1035Dara 1Mutant 1012Thunder1223Juice1211

LivioLanzo Thanks you for your response, i'm sorry , i have another tables which need to be done the same. but this M Code really not bring me quite understanding , Can you please show step by step ? or do i have to all M Code and edit the tables one by one ? it's a mess sorry . Can you guide me step by step how to do it ? Probably i can learn from it and next time no more asking. thanks

Chanleakna123 if your other tables follow the same structure / logic, all you need to change is the first step called Source and point it to your raw table

hi LivioLanzo sorry i have different source which is different template, i mean the column are more than that . i tried using yours , but fail to get it once i input the other source. Can you help me to do step by step on this ? then i can learn from it too. i'm new here in BI . thanks you

How many columns have you got? Can you show a beter sample? Chanleakna123

Need Help !!! Unpivot multiples columns are not success

22 Replies

LivioLanzo

Solution Sage

7 years ago

Chanleakna123

try this M Code

let

    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8klNzM5LPLRASUfJCIid87NTwRxDEM8YiE2A2C0xrwSixtAAKmwKxMEFRZklqaiisTrRSi6JRVDVQOxbWgLUjdBsCLUpJKM0LyW1CGYVzDqv0szkVJigIRjHxgIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Name = _t, Line = _t, #"Material Name" = _t, Quantity = _t, Row = _t, Pallets = _t, #"Material Name 1" = _t, #"Quantity 1" = _t, #"Row 1" = _t, #"Pallets 1" = _t, #"Material Name 2" = _t, #"Quantity 2" = _t, #"Row 2" = _t, #"Pallets 2" = _t]),
    
    
    ChangedType = Table.TransformColumnTypes(
                                Source,
                                {
                                        {"Name", type text}, 
                                        {"Line", Int64.Type}, 
                                        {"Material Name", type text}, 
                                        {"Quantity", Int64.Type}, 
                                        {"Row", Int64.Type}, 
                                        {"Pallets", Int64.Type}, 
                                        {"Material Name 1", type text}, 
                                        {"Quantity 1", Int64.Type}, 
                                        {"Row 1", Int64.Type}, 
                                        {"Pallets 1", Int64.Type}, 
                                        {"Material Name 2", type text}, 
                                        {"Quantity 2", Int64.Type}, 
                                        {"Row 2", Int64.Type}, 
                                        {"Pallets 2", Int64.Type}
                                }
                                 ),
                                 
                                 
     Rowcount = Table.RowCount(ChangedType),
     
     Records = 
            List.Accumulate(
                { 0..Rowcount - 1 },
                {},
                (state, current) => 
                       let
                            Rec1 = Record.SelectFields( ChangedType{current}, {"Name", "Line", "Material Name", "Quantity", "Row", "Pallets"} ),
                            Rec2 = Record.RenameFields(
                                        Record.SelectFields( ChangedType{current}, {"Name", "Line", "Material Name 1", "Quantity 1", "Row 1", "Pallets 1"} ),
                                        { {"Material Name 1", "Material Name"}, {"Quantity 1", "Quantity"}, {"Row 1", "Row"}, {"Pallets 1", "Pallets" } }
                                   ),
                            Rec3 = Record.RenameFields(
                                        Record.SelectFields( ChangedType{current}, {"Name", "Line", "Material Name 2", "Quantity 2", "Row 2", "Pallets 2"} ),
                                        { {"Material Name 2", "Material Name"}, {"Quantity 2", "Quantity"}, {"Row 2", "Row"}, {"Pallets 2", "Pallets" } }
                                   ),
                            Combined = List.Combine( { {Rec1}, {Rec2}, {Rec3} } )
                        in
                            List.Combine( {state, Combined } ) 
                                                  
            ),       
                                 
                                 
   Final = Table.FromRecords ( 
                    Records, type 
                        table [
                                Name = Text.Type, 
                                Line = Int64.Type, 
                                #"Material Name" = Text.Type, 
                                Quantity = Number.Type, 
                                Row = Number.Type, 
                                Pallets = Number.Type 
                              ] 
                            )                       
                                 
in
    Final

Chanleakna123
Post Prodigy
7 years ago
LivioLanzo Thanks you for your response,
i'm sorry , i have another tables which need to be done the same. but this M Code really not bring me quite understanding ,
Can you please show step by step ? or do i have to all M Code and edit the tables one by one ? it's a mess sorry .

Can you guide me step by step how to do it ? Probably i can learn from it and next time no more asking.

thanks
- LivioLanzo
  Solution Sage
  7 years ago
  Chanleakna123 if your other tables follow the same structure / logic, all you need to change is the first step called Source and point it to your raw table

Ashish_Mathur
Super User
7 years ago
Hi,

This M code works fine

let
    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"Line", Int64.Type}, {"Material Name", type text}, {"Quanity", Int64.Type}, {"Row", Int64.Type}, {"Pallets", Int64.Type}, {"Material Name 1", type text}, {"Quanity 1", Int64.Type}, {"Row 1", Int64.Type}, {"Pallets 1", Int64.Type}, {"Material Name 2", type text}, {"Quanity 2", Int64.Type}, {"Row 2", Int64.Type}, {"Pallets 2", Int64.Type}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Name", "Line"}, "Attribute", "Value"),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Other Columns", "Attribute", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, true), {"Attribute.1", "Attribute.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Attribute.1", type text}, {"Attribute.2", type text}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"Attribute.2"}),
    #"Replaced Value" = Table.ReplaceValue(#"Removed Columns","Material Name","Material",Replacer.ReplaceText,{"Attribute.1"}),
    #"Added Custom" = Table.AddColumn(#"Replaced Value", "Custom", each if [Attribute.1]="Material" then [Value] else null),
    #"Filled Down" = Table.FillDown(#"Added Custom",{"Custom"}),
    #"Renamed Columns" = Table.RenameColumns(#"Filled Down",{{"Custom", "Material"}}),
    #"Reordered Columns" = Table.ReorderColumns(#"Renamed Columns",{"Name", "Line", "Material", "Attribute.1", "Value"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Reordered Columns",{{"Material", type text}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type2", each ([Attribute.1] <> "Material")),
    #"Pivoted Column" = Table.Pivot(#"Filtered Rows", List.Distinct(#"Filtered Rows"[Attribute.1]), "Attribute.1", "Value")
in
    #"Pivoted Column"

Hope this helps.
- Chanleakna123
  Post Prodigy
  7 years ago
  Ashish_Mathur can you share me PBI File which i can download ? :(
  i copy my source and error still exisit.
  - Chanleakna123
    Post Prodigy
    7 years ago
    Ashish_Mathur
    this is my M Code source and copy from yours. it's error.
    
    let
    Source = Excel.Workbook(File.Contents("C:\Users\hchanleakna\Desktop\Power BI\16-Stock Transfer to WH\Testing V1.xlsx"), null, true),
    Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"Line", Int64.Type}, {"Material Name", type text}, {"Quanity", Int64.Type}, {"Row", Int64.Type}, {"Pallets", Int64.Type}, {"Material Name 1", type text}, {"Quanity 1", Int64.Type}, {"Row 1", Int64.Type}, {"Pallets 1", Int64.Type}, {"Material Name 2", type text}, {"Quanity 2", Int64.Type}, {"Row 2", Int64.Type}, {"Pallets 2", Int64.Type}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Name", "Line"}, "Attribute", "Value"),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Other Columns", "Attribute", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, true), {"Attribute.1", "Attribute.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Attribute.1", type text}, {"Attribute.2", type text}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"Attribute.2"}),
    #"Replaced Value" = Table.ReplaceValue(#"Removed Columns","Material Name","Material",Replacer.ReplaceText,{"Attribute.1"}),
    #"Added Custom" = Table.AddColumn(#"Replaced Value", "Custom", each if [Attribute.1]="Material" then [Value] else null),
    #"Filled Down" = Table.FillDown(#"Added Custom",{"Custom"}),
    #"Renamed Columns" = Table.RenameColumns(#"Filled Down",{{"Custom", "Material"}}),
    #"Reordered Columns" = Table.ReorderColumns(#"Renamed Columns",{"Name", "Line", "Material", "Attribute.1", "Value"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Reordered Columns",{{"Material", type text}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type2", each ([Attribute.1] <> "Material")),
    #"Pivoted Column" = Table.Pivot(#"Filtered Rows", List.Distinct(#"Filtered Rows"[Attribute.1]), "Attribute.1", "Value")
    in
    #"Pivoted Column"

Forum Discussion

Need Help !!! Unpivot multiples columns are not success

22 Replies

Related content

Unpivot Column

Advanced unpivoting multiple columns

Unpivot multiple columns

Unpivot Multiple Columns

Unpivot columns

Name	Line	Material Name	Quanity	Row	Pallets
Leakna	2	Coke	12	3	4
Dara	1	Mutant	10	1	2
Leakna	2	Fanta	10	3	5
Dara	1	Thunder	12	2	3
Leakna	2	Sprite	10	3	5
Dara	1	Juice	12	1	1