Normalizing columns with dates

[Anonymous]

Hi,

When I load an excel file from folder and normalize the data (with dates as column headers), it works fine and load successfully into the model.

However, when I put in another excel file with other dates in the folder and trying to load it just the same way, it doesn't work because Power BI don't recognize the column names (i.e the date).  

Does anyone know how to fix this?

Thanks

Marcus

[AlB]

Hi @Anonymous 

I do not quite understand. In order to clarify what the issue is further, can you show a sample of the data or, ideally share the files you are woking on?

Please mark the question solved when done and consider giving kudos if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[Anonymous]

Sorry for that, I will try to explain better.

I have the data like this, and want to transform in into an "normalized" table instead of matrix:

So I'm transforming it like this with basic Power Query operations, removing some rows and putting date as Column header, and then transformning it like this: 

And it works fine. But when I add another file in the folder, with february dates, in exactly same format, it doesnt work when I'm updating Power BI. It returns 31 new rows with January dates and empty "Line" and "sum".   

[dax]

Hi @Anonymous , 
You could try below M code to see whether it work or not(I simplify your data, so my data is not same as your, you could click "source" step to see my sample, then change my steps and apply this on your sample)

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WyivNyVHSIUzF6kQrFaQWEasUyjcyMDLQN9Q3RDCNEExjBNMEwTRF1o+C8ImDbDA0ABEglikQWwKxBVgOYiWIALHMoPJGYDmQG4xBcsZg1wAxyCmGSrGxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t, Column5 = _t, Column6 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type","",null,Replacer.ReplaceValue,{"Column2", "Column3", "Column4", "Column5", "Column6"}),
    #"Filled Down" = Table.FillDown(#"Replaced Value",{"Column2", "Column3", "Column4", "Column5", "Column6"}),
    #"Removed Top Rows" = Table.Skip(#"Filled Down",4),
    #"Promoted Headers" = Table.PromoteHeaders(#"Removed Top Rows", [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"null", Int64.Type}, {"2020/1/1", Int64.Type}, {"2020/1/2", Int64.Type}, {"2020/1/3", Int64.Type}, {"2020/1/4", Int64.Type}, {"2020/1/5", Int64.Type}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type1",{{"null", "ID"}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Renamed Columns", {"ID"}, "Attribute", "Value"),
    #"Renamed Columns1" = Table.RenameColumns(#"Unpivoted Other Columns",{{"Attribute", "Date"}, {"Value", "Sum"}})
in
    #"Renamed Columns1"

Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[mahoneypat]

Can you share the M code from the Advanced Editor?  You likely have a step that specifically lists the January dates (from when you created your query from the first file).  

Regards,

Pat

[Anonymous]

Here it is. Thanks for helping a newbie out.

Sorry for Swedish and picture..