Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Dear all,
I am really struggeling with transforming data from a data provider I have access to. All data comes in csv/xml on the format below;
My goal is to set up a routine in power query / power bi to transform/unpivot this structure into a "database format";
The biggest challenge is that the the routine needs to be scalable. In other words;
I have spent way to much time trying to figure this out, both through books and googling - so this is my last resort.
In advance, thank you very much!
Solved! Go to Solution.
@Anonymous,
From the sample data you provided, as it’s not a standard data structure which could be unpivoted, so it’s not able to meet your requirement dynamically via M query. You can refer to below steps:
1. Add a new blank query and paste the following code to the Advanced Editor of the blank query. Assume the CSV is named PIVOTTest.
let Source = Csv.Document(File.Contents("YourPath\PIVOTTest.csv"),[Delimiter=",", Columns=10, Encoding=1252, QuoteStyle=QuoteStyle.None]), #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]), #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"CustomerID", type text}, {"Revenue", Int64.Type}, {"Revenue(-1y)", Int64.Type}, {"Revenue(-2y)", Int64.Type}, {"Accounts Payable", Int64.Type}, {"Accounts Payable(-1y)", Int64.Type}, {"Accounts Payable(-2y)", Int64.Type}, {"Year", Int64.Type}, {"Year(-1y)", Int64.Type}, {"Year(-2y)", Int64.Type}}), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"CustomerID"}, "Attribute", "Value"), #"Replaced Value" = Table.ReplaceValue(#"Unpivoted Other Columns","(-1y)","",Replacer.ReplaceText,{"Attribute"}), #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","(-2y)","",Replacer.ReplaceText,{"Attribute"}), #"Added Index" = Table.AddIndexColumn(#"Replaced Value1", "Index", 0, 1), #"Pivoted Column" = Table.Pivot(#"Added Index", List.Distinct(#"Added Index"[Attribute]), "Attribute", "Value") in #"Pivoted Column"
2. Add a new blank query and paste the following code to the Advanced Editor of the blank query
let Source = PIVOTTest, #"Removed Columns" = Table.RemoveColumns(Source,{"Index"}), #"Filtered Rows" = Table.SelectRows(#"Removed Columns", each ([Accounts Payable] <> null)), #"Removed Columns1" = Table.RemoveColumns(#"Filtered Rows",{"Revenue", "Year"}), #"Added Index" = Table.AddIndexColumn(#"Removed Columns1", "Index", 0, 1) in #"Added Index"
3. Add a new blank query and paste the following code to the Advanced Editor of the blank query.
let Source = PIVOTTest, #"Removed Columns" = Table.RemoveColumns(Source,{"Index"}), #"Filtered Rows" = Table.SelectRows(#"Removed Columns", each [Year] <> null), #"Removed Columns1" = Table.RemoveColumns(#"Filtered Rows",{"Revenue", "Accounts Payable"}), #"Added Index" = Table.AddIndexColumn(#"Removed Columns1", "Index", 0, 1) in #"Added Index"
4. Add a new blank query and paste the following code to the Advanced Editor of the blank query.
let Source = PIVOTTest, #"Removed Columns" = Table.RemoveColumns(Source,{"Index"}), #"Filtered Rows" = Table.SelectRows(#"Removed Columns", each [Revenue] <> null), #"Removed Columns1" = Table.RemoveColumns(#"Filtered Rows",{"Accounts Payable", "Year"}), #"Added Index" = Table.AddIndexColumn(#"Removed Columns1", "Index", 0, 1), #"Merged Queries" = Table.NestedJoin(#"Added Index",{"Index"},#"PIVOTTest (3)",{"Index"},"PIVOTTest (3)",JoinKind.LeftOuter), #"Expanded PIVOTTest (3)" = Table.ExpandTableColumn(#"Merged Queries", "PIVOTTest (3)", {"Accounts Payable"}, {"PIVOTTest (3).Accounts Payable"}), #"Merged Queries1" = Table.NestedJoin(#"Expanded PIVOTTest (3)",{"Index"},#"PIVOTTest (4)",{"Index"},"PIVOTTest (4)",JoinKind.LeftOuter), #"Expanded PIVOTTest (4)" = Table.ExpandTableColumn(#"Merged Queries1", "PIVOTTest (4)", {"Year"}, {"PIVOTTest (4).Year"}), #"Removed Columns2" = Table.RemoveColumns(#"Expanded PIVOTTest (4)",{"Index"}), #"Renamed Columns" = Table.RenameColumns(#"Removed Columns2",{{"PIVOTTest (3).Accounts Payable", "Accounts Payable"}, {"PIVOTTest (4).Year", "Year"}}) in #"Renamed Columns"
Regards,
Lydia
@Anonymous,
From the sample data you provided, as it’s not a standard data structure which could be unpivoted, so it’s not able to meet your requirement dynamically via M query. You can refer to below steps:
1. Add a new blank query and paste the following code to the Advanced Editor of the blank query. Assume the CSV is named PIVOTTest.
let Source = Csv.Document(File.Contents("YourPath\PIVOTTest.csv"),[Delimiter=",", Columns=10, Encoding=1252, QuoteStyle=QuoteStyle.None]), #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]), #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"CustomerID", type text}, {"Revenue", Int64.Type}, {"Revenue(-1y)", Int64.Type}, {"Revenue(-2y)", Int64.Type}, {"Accounts Payable", Int64.Type}, {"Accounts Payable(-1y)", Int64.Type}, {"Accounts Payable(-2y)", Int64.Type}, {"Year", Int64.Type}, {"Year(-1y)", Int64.Type}, {"Year(-2y)", Int64.Type}}), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"CustomerID"}, "Attribute", "Value"), #"Replaced Value" = Table.ReplaceValue(#"Unpivoted Other Columns","(-1y)","",Replacer.ReplaceText,{"Attribute"}), #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","(-2y)","",Replacer.ReplaceText,{"Attribute"}), #"Added Index" = Table.AddIndexColumn(#"Replaced Value1", "Index", 0, 1), #"Pivoted Column" = Table.Pivot(#"Added Index", List.Distinct(#"Added Index"[Attribute]), "Attribute", "Value") in #"Pivoted Column"
2. Add a new blank query and paste the following code to the Advanced Editor of the blank query
let Source = PIVOTTest, #"Removed Columns" = Table.RemoveColumns(Source,{"Index"}), #"Filtered Rows" = Table.SelectRows(#"Removed Columns", each ([Accounts Payable] <> null)), #"Removed Columns1" = Table.RemoveColumns(#"Filtered Rows",{"Revenue", "Year"}), #"Added Index" = Table.AddIndexColumn(#"Removed Columns1", "Index", 0, 1) in #"Added Index"
3. Add a new blank query and paste the following code to the Advanced Editor of the blank query.
let Source = PIVOTTest, #"Removed Columns" = Table.RemoveColumns(Source,{"Index"}), #"Filtered Rows" = Table.SelectRows(#"Removed Columns", each [Year] <> null), #"Removed Columns1" = Table.RemoveColumns(#"Filtered Rows",{"Revenue", "Accounts Payable"}), #"Added Index" = Table.AddIndexColumn(#"Removed Columns1", "Index", 0, 1) in #"Added Index"
4. Add a new blank query and paste the following code to the Advanced Editor of the blank query.
let Source = PIVOTTest, #"Removed Columns" = Table.RemoveColumns(Source,{"Index"}), #"Filtered Rows" = Table.SelectRows(#"Removed Columns", each [Revenue] <> null), #"Removed Columns1" = Table.RemoveColumns(#"Filtered Rows",{"Accounts Payable", "Year"}), #"Added Index" = Table.AddIndexColumn(#"Removed Columns1", "Index", 0, 1), #"Merged Queries" = Table.NestedJoin(#"Added Index",{"Index"},#"PIVOTTest (3)",{"Index"},"PIVOTTest (3)",JoinKind.LeftOuter), #"Expanded PIVOTTest (3)" = Table.ExpandTableColumn(#"Merged Queries", "PIVOTTest (3)", {"Accounts Payable"}, {"PIVOTTest (3).Accounts Payable"}), #"Merged Queries1" = Table.NestedJoin(#"Expanded PIVOTTest (3)",{"Index"},#"PIVOTTest (4)",{"Index"},"PIVOTTest (4)",JoinKind.LeftOuter), #"Expanded PIVOTTest (4)" = Table.ExpandTableColumn(#"Merged Queries1", "PIVOTTest (4)", {"Year"}, {"PIVOTTest (4).Year"}), #"Removed Columns2" = Table.RemoveColumns(#"Expanded PIVOTTest (4)",{"Index"}), #"Renamed Columns" = Table.RenameColumns(#"Removed Columns2",{{"PIVOTTest (3).Accounts Payable", "Accounts Payable"}, {"PIVOTTest (4).Year", "Year"}}) in #"Renamed Columns"
Regards,
Lydia
This is just perfect - thank you!!!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
98 | |
86 | |
70 | |
62 |
User | Count |
---|---|
151 | |
120 | |
103 | |
87 | |
68 |