Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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 | 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 |
Below is my originated table , how to create it like the table above
Name | Line | Material Name | Quanity | Row | Pallets | Material Name 1 | Quanity 1 | Row 1 | Pallets 1 | Material Name 2 | Quanity 2 | Row 2 | Pallets 2 |
Leakna | 2 | Coke | 12 | 3 | 4 | Fanta | 10 | 3 | 5 | Sprite | 10 | 3 | 5 |
Dara | 1 | Mutant | 10 | 1 | 2 | Thunder | 12 | 2 | 3 | Juice | 12 | 1 | 1 |
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.
@Ashish_Mathur can you share me PBI File which i can download ? 😞
i copy my source and error still exisit.
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"
@Ashish_Mathur https://drive.google.com/file/d/1mPh8GjFnIHiMuMgaGE_yJniloj1RdO76/view?usp=sharing
above is my tables which have error after using the unpivot steps.
thanks for your time. really appreciated.
Hi,
Why have you shared 2 worksheets there? Which one should i consider? There will be different solutions for both. Share only 1 worksheet and include specifically the rows where you are facing errors with my previously shared M code.
Hi,
Refer to the third worksheet in this workbook.
@Ashish_Mathur this is what i'm looking for. but how to achive this in BI ?
i pivot the columns the those turn some error and some are now.
thanks with Regards,
Hi,
Start PowerBI desktop and go to File > Import > Excel workbook. My Excel solution will import in PowerBI desktop.
Hi,
You may download my PBI file from here.
Hope this helps.
@Ashish_Mathur you're amazing , i follow the instruction and it works most of it .
but i got error on few columns, below is my M Code.
can you assist on thiis?
let
Source = Csv.Document(Web.Contents("https://docs.google.com/spreadsheets/d/e/2PACX-1vQGwLNNPqXIrMNiEsL06MA2QDR6XPg0huxYPv-JcEx3kxwg8J1CG...",", Columns=60, QuoteStyle=QuoteStyle.None]),
#"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Submission Date", type datetime}, {"FG Transfer", type text}, {"Date", type text}, {"Entry Your ID below", type text}, {"Assign Person to Confirm the Transfers :", type text}, {"Line", type text}, {"Shift", Int64.Type}, {"Please Choose Below options for your acknowledgement to Confirm the Transfers:", type text}, {"How many SKUs will you transfer to Warehouse today ?", type text}, {"Products Name :", type text}, {"Manufacturing Date :", type text}, {"Expiring Date :", type text}, {"Row No. :", type text}, {"Pallets :", type text}, {"Row No. : 2", type text}, {"Pallets : 2", type text}, {"Row No. : 3", type text}, {"Pallets : 3", type text}, {"Row No. : 4", Int64.Type}, {"Pallets : 4", Int64.Type}, {"QTY/Cases :", Int64.Type}, {"Total Pallets :", Int64.Type}, {"Total Cases/Pallet 1 :", Int64.Type}, {"Total Cases 1st SKU :", type text}, {"Products Name : 2", type text}, {"Manufacturing Date : 2", type text}, {"Expiring Date : 2", type text}, {"Row No. : 5", type text}, {"Pallets : 5", type text}, {"Row No. : 6", type text}, {"Pallets : 6", type text}, {"Row No. : 7", type text}, {"Pallets : 7", type text}, {"Row No. : 8", Int64.Type}, {"Pallets : 8", Int64.Type}, {"QTY/Cases : 2", type text}, {"Total Pallets : 2", Int64.Type}, {"Total Cases/Pallets 2 :", Int64.Type}, {"Total Cases 2nd SKU :", type text}, {"Products Name : 3", type text}, {"Manufacturing Date : 3", type text}, {"Expiring Date : 3", type text}, {"Row No. : 9", type text}, {"Pallets : 9", type text}, {"Row No. : 10", type text}, {"Pallets : 10", type text}, {"Row No. : 11", type text}, {"Pallets : 11", type text}, {"Row No. : 12", Int64.Type}, {"Pallets : 12", Int64.Type}, {"QTY/Cases : 3", type text}, {"Total Pallets : 3", Int64.Type}, {"Total Cases/Pallets 3 :", Int64.Type}, {"Total Cases 3rd SKU :", Int64.Type}, {"Total All Cases for these 3 SKUs :", Int64.Type}, {"Signature :", type text}, {"Get Page URL", type text}, {"IP", type text}, {"Submission ID", type number}, {"Edit Link", type text}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Signature :", "Get Page URL", "IP", "Submission ID", "Edit Link"}),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Removed Columns", {"Submission Date", "FG Transfer", "Date", "Entry Your ID below", "Assign Person to Confirm the Transfers :", "Line", "Shift", "Please Choose Below options for your acknowledgement to Confirm the Transfers:", "How many SKUs will you transfer to Warehouse today ?"}, "Attribute", "Value"),
#"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Other Columns", "Attribute", Splitter.SplitTextByDelimiter(" :", QuoteStyle.Csv), {"Attribute.1", "Attribute.2"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Attribute.1", type text}, {"Attribute.2", Int64.Type}}),
#"Split Column by Delimiter1" = Table.SplitColumn(#"Changed Type1", "Attribute.1", Splitter.SplitTextByDelimiter(".", QuoteStyle.Csv), {"Attribute.1.1", "Attribute.1.2"}),
#"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"Attribute.1.1", type text}, {"Attribute.1.2", type text}}),
#"Removed Columns1" = Table.RemoveColumns(#"Changed Type2",{"Attribute.1.2", "Attribute.2"}),
#"Replaced Value" = Table.ReplaceValue(#"Removed Columns1","Total Cases/Pallet 1","Total Cases/Pallet",Replacer.ReplaceText,{"Attribute.1.1"}),
#"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","Total Cases 1st SKU","Total Cases",Replacer.ReplaceText,{"Attribute.1.1"}),
#"Added Conditional Column" = Table.AddColumn(#"Replaced Value1", "Product", each if [Attribute.1.1] = "Products Name" then [Value] else null),
#"Filled Down" = Table.FillDown(#"Added Conditional Column",{"Product"}),
#"Renamed Columns" = Table.RenameColumns(#"Filled Down",{{"Product", "Product Name"}}),
#"Reordered Columns" = Table.ReorderColumns(#"Renamed Columns",{"Submission Date", "FG Transfer", "Date", "Entry Your ID below", "Assign Person to Confirm the Transfers :", "Line", "Shift", "Please Choose Below options for your acknowledgement to Confirm the Transfers:", "How many SKUs will you transfer to Warehouse today ?", "Product Name", "Attribute.1.1", "Value"}),
#"Filtered Rows1" = Table.SelectRows(#"Reordered Columns", each ([Product Name] <> "")),
#"Pivoted Column" = Table.Pivot(#"Filtered Rows1", List.Distinct(#"Filtered Rows1"[Attribute.1.1]), "Attribute.1.1", "Value"),
#"Filtered Rows" = Table.SelectRows(#"Pivoted Column", each ([Manufacturing Date] = "16-10-2018" or [Manufacturing Date] = "17-10-2018"))
in
#"Filtered Rows"
You are welcome. Share the link from where i can download your PBI file.
Hi,
See if this works.
HI @Ashish_Mathur , When i filer to the data i need on Expiring Date , it shows the date but the other column like Row and Pallet turns null , this is not work 😞
and what is Partition Steps there means ?
i'm not quiet understand
Hi,
The partition step creates a Index number for each change in the Attribute.1.1 column. OK, let;s do it this way. Share a smaller sample dataset and show me the exact result that you are expecting. In your sample dataset, please take into account the rows where you are getting the error. Once i get to know the actual values that you are expecting in the two columns (which are currently giving you errors), i will try to change my solution.
Hi
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
Did I answer your question correctly? Mark my answer as a solution!
Proud to be a Datanaut!
@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
Did I answer your question correctly? Mark my answer as a solution!
Proud to be a Datanaut!
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
User | Count |
---|---|
96 | |
87 | |
78 | |
72 | |
68 |
User | Count |
---|---|
113 | |
105 | |
84 | |
65 | |
64 |