Remodeling Data in Power Query, data not normalized

[wallace158]

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!

[MBreden]

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

[Daryl-Lynch-Bzy]

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]

Hi @Daryl-Lynch-Bzy ,

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]

@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]

Cool. Looks like you've got everything you need then 🙂👍

Pete

[BA_Pete · Accepted solution]

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]

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]

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

[wallace158]

👏👏👏thank you so much! I understand now. yes this works great and I was able to break down the steps into different table in a blank .pbi file so I could see it clearly. is there a good way to link the file in here for others?

[BA_Pete]

Hi @wallace158 ,

I don't think there's any need to link your file here.

The beauty of the code I shared is that anybody can just copy and paste it into their own Power Query if they want to follow the steps.

Pete