Hello all, I am working with our companies financial team and they have our budget organized with a sales account and total sales for each month with each month having its own column. It Looks like thisCustomerJan SalesFeb SalesMar SalesApril SalesMay SalesJune SalesJuly SalesCust 15212132516132131214123211258Cust 25611312131531611517496515261Cust 32156151665165115616516513218656161 I am trying to normalize the data to look like this:CustomerMonthSalesCust 11521Cust 122132Cust 21561Cust 22131 Currently, I have figured out how to do this but would love to hear if there is a better way. My Current Technique:Merge all the columns into 1 column, called 'merged col', with a delimiter ";".Create a custom column with the following M code: = let templist = Text.split([merged_col],";") final = "1," & templist{0},"2," & templist{1},"3," & templist{2},"4," & templist{3},"5," & templist{4},"6," & templist{5},"7," & templist{6},"8," & templist{7},"9," & templist{8},"10," & templist{9},"11," & templist{10},"12," & templist{11} in final This creates a column that looks like this for Cust 1: 1,521;2,2132;3,5161;4,3213;5,1214;6,1232;7,11258 etc, it basically creates a list out of the merged column, and then adds a month id to each value within the ";" delimiter, using "," as a secondary delimiter.I can then "split columns" on ";" and split to rows.Then split columns on "," and split to columns.Then I delete the old Jan,Feb,Mar Sales ColumnsThat gives me the desired result.This seems to be kinda weird, so I would love to see if anyone has a slick way to accomplish this. Thanks guys!

Forum Discussion

Frequent Visitor

4 years ago

Solved

Remodeling Data in Power Query, data not normalized

Hello all,

I am working with our companies financial team and they have our budget organized with a sales account and total sales for each month with each month having its own column. It Looks like this

Customer	Jan Sales	Feb Sales	Mar Sales	April Sales	May Sales	June Sales	July Sales
Cust 1	521	2132	5161	3213	1214	1232	11258
Cust 2	561	131	21315	3161	1517	4965	15261
Cust 3	2156	1516	651651	1561	651651	32186	56161

I am trying to normalize the data to look like this:

Customer	Month	Sales
Cust 1	1	521
Cust 1	2	2132
Cust 2	1	561
Cust 2	2	131

Currently, I have figured out how to do this but would love to hear if there is a better way. My Current Technique:

Merge all the columns into 1 column, called 'merged col', with a delimiter ";".
Create a custom column with the following M code:

= let
  templist = Text.split([merged_col],";")
  final = "1," & templist{0},"2," & templist{1},"3," & templist{2},"4," & templist{3},"5," & templist{4},"6," & templist{5},"7," & templist{6},"8," & templist{7},"9," & templist{8},"10," & templist{9},"11," & templist{10},"12," & templist{11}
in
final

This creates a column that looks like this for Cust 1: 1,521;2,2132;3,5161;4,3213;5,1214;6,1232;7,11258 etc, it basically creates a list out of the merged column, and then adds a month id to each value within the ";" delimiter, using "," as a secondary delimiter.
I can then "split columns" on ";" and split to rows.
Then split columns on "," and split to columns.
Then I delete the old Jan,Feb,Mar Sales Columns
That gives me the desired result.

This seems to be kinda weird, so I would love to see if anyone has a slick way to accomplish this. Thanks guys!

Hi wallace158 ,

I would do it this way. It should be a bit more dynamic than your current solution which appears to rely on the data being in a specific order to allocate the month numbers correctly. It will also be much clearer and easier to understand for future devs of your report:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("PY67EcAgDENXybmmkcEOqTMGxxRJ9o8/HI11wnoyY9D9Pe8BKiTsk1HZDdRdNWsCRguJHcDSaZbFRjzSqKsB4mxWQHCatEslHNvrZmvkRTPnoiaSXODb21e65ilvmD8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Customer = _t, #"Jan Sales" = _t, #"Feb Sales" = _t, #"Mar Sales" = _t, #"April Sales" = _t, #"May Sales" = _t, #"June Sales" = _t, #"July Sales" = _t]),
    unpivOthCols = Table.UnpivotOtherColumns(Source, {"Customer"}, "Attribute", "Sales"),
    splitAttribBySpace = Table.SplitColumn(unpivOthCols, "Attribute", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Attribute.1", "Attribute.2"}),
    extractFirstThreeChars = Table.TransformColumns(splitAttribBySpace, {{"Attribute.1", each Text.Start(_, 3), type text}}),
    addMonth = Table.AddColumn(extractFirstThreeChars, "month", each
if [Attribute.1] = "Jan" then 1
else if [Attribute.1] = "Feb" then 2
else if [Attribute.1] = "Mar" then 3
else if [Attribute.1] = "Apr" then 4
else if [Attribute.1] = "May" then 5
else if [Attribute.1] = "Jun" then 6
else if [Attribute.1] = "Jul" then 7
else if [Attribute.1] = "Aug" then 8
else if [Attribute.1] = "Sep" then 9
else if [Attribute.1] = "Oct" then 10
else if [Attribute.1] = "Nov" then 11
else if [Attribute.1] = "Dec" then 12
else null),
    remOthCols = Table.SelectColumns(addMonth,{"Customer", "month", "Sales"})
in
    remOthCols

Summary:

1) Unpivot columns other than [customer], changing the "Value" name to "Sales" in the formula bar

2) Split new [attribute] column by space ( " " ) delimiter

3) Extract first three characters of current month name (to simplify next step)

4) Add [month] column using conditional evaluation

5) Remove columns other than those required

My output:

Pete

10 Replies

Anonymous
Not applicable
4 years ago
Hi wallace158 - I would recommend using "Date" instead of "Month Number", it would provide to be more flexible in the future.
let
#"Year" = 2022,
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("PY67EcAgDENXybmmkcEOqTMGxxRJ9o8/HI11wnoyY9D9Pe8BKiTsk1HZDdRdNWsCRguJHcDSaZbFRjzSqKsB4mxWQHCatEslHNvrZmvkRTPnoiaSXODb21e65ilvmD8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Customer = _t, #"Jan Sales" = _t, #"Feb Sales" = _t, #"Mar Sales" = _t, #"April Sales" = _t, #"May Sales" = _t, #"June Sales" = _t, #"July Sales" = _t]),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Customer"}, "Month", "Sales"),
#"Extracted First Characters" = Table.TransformColumns(#"Unpivoted Other Columns", {{"Month", each Text.Start(_, 3), type text}}),
#"Added Custom" = Table.AddColumn(#"Extracted First Characters", "Correct Month", each Date.FromText( Text.From(#"Year") & "-" & [Month] & "-01"), type date)
in
#"Added Custom"
- BA_Pete
  Super User
  4 years ago
  Hi Anonymous ,
  
  I totally agree that using a proper date would be the ideal scenario.
  
  The issue I had was that OP didn't imply there was a date field in their data, so you actually lose flexibility by now having a hardcoded year in the query.
  
  wallace158 Do you have a year datapoint anywhere in your source that could be dynamically applied to this to create proper dates?
  
  Pete
  - wallace158
    Frequent Visitor
    4 years ago
    BA_Pete, yes I actually do. Because the original data source has all the months in different columns, there is a column with "22". So once I finish my above method and add the month number in a column, I create a new field with "#date(2000+[year],[month],1). Then I link this new date field to the calendar dimension. I should have had this in my original question.
BA_Pete
Super User
4 years ago
Hi wallace158 ,

I would do it this way. It should be a bit more dynamic than your current solution which appears to rely on the data being in a specific order to allocate the month numbers correctly. It will also be much clearer and easier to understand for future devs of your report:

let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("PY67EcAgDENXybmmkcEOqTMGxxRJ9o8/HI11wnoyY9D9Pe8BKiTsk1HZDdRdNWsCRguJHcDSaZbFRjzSqKsB4mxWQHCatEslHNvrZmvkRTPnoiaSXODb21e65ilvmD8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Customer = _t, #"Jan Sales" = _t, #"Feb Sales" = _t, #"Mar Sales" = _t, #"April Sales" = _t, #"May Sales" = _t, #"June Sales" = _t, #"July Sales" = _t]), unpivOthCols = Table.UnpivotOtherColumns(Source, {"Customer"}, "Attribute", "Sales"), splitAttribBySpace = Table.SplitColumn(unpivOthCols, "Attribute", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Attribute.1", "Attribute.2"}), extractFirstThreeChars = Table.TransformColumns(splitAttribBySpace, {{"Attribute.1", each Text.Start(_, 3), type text}}), addMonth = Table.AddColumn(extractFirstThreeChars, "month", each if [Attribute.1] = "Jan" then 1 else if [Attribute.1] = "Feb" then 2 else if [Attribute.1] = "Mar" then 3 else if [Attribute.1] = "Apr" then 4 else if [Attribute.1] = "May" then 5 else if [Attribute.1] = "Jun" then 6 else if [Attribute.1] = "Jul" then 7 else if [Attribute.1] = "Aug" then 8 else if [Attribute.1] = "Sep" then 9 else if [Attribute.1] = "Oct" then 10 else if [Attribute.1] = "Nov" then 11 else if [Attribute.1] = "Dec" then 12 else null), remOthCols = Table.SelectColumns(addMonth,{"Customer", "month", "Sales"}) in remOthCols

Summary:

1) Unpivot columns other than [customer], changing the "Value" name to "Sales" in the formula bar

2) Split new [attribute] column by space ( " " ) delimiter

3) Extract first three characters of current month name (to simplify next step)

4) Add [month] column using conditional evaluation

5) Remove columns other than those required

My output:

Pete
- wallace158
  Frequent Visitor
  4 years ago
  Wow BA_Pete, this looks really good! but I don't understand the first row in the source table, "json.document(binary.fromtext", I'm reading about it now but having trouble understanding why it should be in the first row?
  - BA_Pete
    Super User
    4 years ago
    Hi wallace158 ,
    
    I copied your example table into 'Enter Data' in Power Query. When you use this feature, it's built/stored as a JSON binary (in text format), so this is what you are seeing as the source. It makes it easier for you to copy the whole M code I provided and paste it into a new blank query using Avanced Editor in PQ. This gives you a working example in PQ for you to observe/test the effect of each of my steps.
    
    On your own data, you would effectively start from my 'unpivOthCols' step.
    
    Pete

MBreden

Helper I

4 years ago

Another dynamic solution with the date of the current year:

let
    Quelle = Excel.CurrentWorkbook(){[Name="wallace158"]}[Content],
    #"Entpivotierte andere Spalten" = Table.UnpivotOtherColumns(Quelle, {"Customer"}, "Date", "Sales"),
    #"Ersetzter Wert" = Table.ReplaceValue(#"Entpivotierte andere Spalten", "Sales",Number.ToText( Date.Year(DateTime.LocalNow())),Replacer.ReplaceText,{"Date"}),
    #"Geänderter Typ" = Table.TransformColumnTypes(#"Ersetzter Wert",{{"Date", type date}, {"Customer", type text}, {"Sales", Int64.Type}})
in
    #"Geänderter Typ"

Melanie

Forum Discussion

Remodeling Data in Power Query, data not normalized

10 Replies

Related content

Normal Distribution

Display Chart with Normalized Values

normalization

Remodeling bars graphic.

merging direct query queries